Best practice for displaying and calculating data in a pivot table?

Question

I have a data table ('Legal Fees') with three columns:

• Date
• Attorney Name
• Hours Billed

I have another data table ('Billing Rates') with two columns:

• Attorney Name
• Hourly Billing Rate

I would like to display a summary of the information in a pivot table that shows:

• Attorney Name
• Hours Billed
• Hourly Rate
• Dollar Amount Billed (hours*rate)

I realize I can do this right on the data table pretty easily with a VLOOKUP to the 'Rate' table and another column multiplying the hours billed by the rate, but as an exercise, I'd like to do this without manipulating the data tables at all, only using a pivot table to summarize the data.

What is the best practice for:

1. Displaying the attorney's billing rate (which is constant for each attorney) on the pivot table (without inserting a column on the 'Legal Fees' data table), and - not possible with vanilla Excel per /u/agtoever
2. Displaying the calculated field (hours*rate) on the pivot table without calculating it on the data-table first (so the PT is actually doing the calculation)

Thanks!

Answer 1

In database-terms, what you're trying to do is to JOIN to tables before you are compiling the data into a pivot table. This can only be done by using VLOOKUP or similar functions on the source tables. The Excel Pivot table can only take one table as an input, not more.

I think there is a (free) alternative to what you're trying to do and it's called Power Pivot. This is an addon to Excel, what gives you database-like features, such as performing a JOIN on tables before giving the resulting data to a regular pivot table. This feature is called "create relations between tables" and is pretty much self-explanatory. You should import the two tables as data sources and then create a relationship. After that, you can create the pivot table your mentioned.