Combine Pivot and regular table (on Mac)

%3CLINGO-SUB%20id%3D%22lingo-sub-1321035%22%20slang%3D%22en-US%22%3ECombine%20Pivot%20and%20regular%20table%20(on%20Mac)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321035%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20an%20example%20of%20what%20I%20want%20to%20do%3A%3C%2FP%3E%3COL%3E%3CLI%3EI%20have%20Sales%20in%20a%20table.%3C%2FLI%3E%3CLI%3EI%20use%20a%20Pivot%20to%20get%20sum%20of%20Sales%3C%2FLI%3E%3CLI%3EI%20then%20want%20to%20compare%20Sales%20with%20a%20target%20(this%20is%20a%20regular%20table).%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20copy%20the%20Pivot%20from%20%232%20to%20a%20new%20table%20and%20copy%20the%20targets%20to%20the%20same%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20better%20way%20to%20do%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20attached%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1321035%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321062%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20Pivot%20and%20regular%20table%20(on%20Mac)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F575648%22%20target%3D%22_blank%22%3E%40nilanjenator%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20thought%20for%20you.%20Pivot%20Tables%20work%20off%20a%20single%20table.%20There%20is%20Power%20Pivot%20now%2C%20by%20the%20way%2C%20depending%20on%20what%20version%20of%20Excel%20you're%20using%2C%20and%20Power%20Pivot%20can%20combine%20two%20or%20more%20tables%20into%20a%20single%20Pivot%20Table...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20assuming%20you%20don't%20have%20access%20to%20that%2C%20you%20can%20still%20combine%20your%20Target%20table%20data%20with%20the%20Sales%20table%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20something%20similar%20in%20our%20household%20personal%20expense%20tracking%20spreadsheet.%20Cash%20in%20and%20Cash%20out%20transactions%20are%20combined%20in%20one%20table%2C%20I%20just%20have%20the%20different%20transactions%20in%20different%20categories%20(the%20vertical%20axis)%2C%20with%20all%20income%20at%20the%20top...%20And%20income%20transactions%20are%20recorded%20as%20positive%20numbers%3B%20expenses%20are%20negative%2C%20so%20the%20difference%2C%20the%20delta%20between%20Income%20and%20Outflow%20is%20basically%20captured%20by%20the%20Grand%20Total%20on%20the%20Bottom%20line.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20not%20exactly%20what%20you're%20trying%20to%20do%2C%20I%20know.%20I'm%20just%20suggesting%20it%20as%20a%20thought%20experiment%20for%20you.%20There%20would%20be%20variations%20on%20it%2C%20for%20sure.%20For%20example%2C%20if%20you're%20tracking%20sales%20on%20a%20monthly%20basis%2C%20arrayed%20across%20the%20horizontal%20axis%20(the%20columns)%20of%20your%20Pivot%20Table%2C%20you%20could%20designate%20the%20Target%20figures%20and%20actual%20Sales%20figures%20as%20JanTar%2C%20JanSls%2C%20FebTar%2C%20FebSls%2C%20etc.%2C%20as%20a%20subordinate%20category%20to%20the%20months%20themselves%2C%20and%20have%20them%20displayed%20side-by-side....and%20so%20forth.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBe%20creative.%20Have%20fun.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

This is an example of what I want to do:

  1. I have Sales in a table.
  2. I use a Pivot to get sum of Sales
  3. I then want to compare Sales with a target (this is a regular table).

 

I currently copy the Pivot from #2 to a new table and copy the targets to the same table.

 

Is there a better way to do this.

 

Sample attached 

1 Reply
Highlighted

@nilanjenator 

 

A thought for you. Pivot Tables work off a single table. There is Power Pivot now, by the way, depending on what version of Excel you're using, and Power Pivot can combine two or more tables into a single Pivot Table...

 

But assuming you don't have access to that, you can still combine your Target table data with the Sales table data.

 

I do something similar in our household personal expense tracking spreadsheet. Cash in and Cash out transactions are combined in one table, I just have the different transactions in different categories (the vertical axis), with all income at the top... And income transactions are recorded as positive numbers; expenses are negative, so the difference, the delta between Income and Outflow is basically captured by the Grand Total on the Bottom line.

 

That's not exactly what you're trying to do, I know. I'm just suggesting it as a thought experiment for you. There would be variations on it, for sure. For example, if you're tracking sales on a monthly basis, arrayed across the horizontal axis (the columns) of your Pivot Table, you could designate the Target figures and actual Sales figures as JanTar, JanSls, FebTar, FebSls, etc., as a subordinate category to the months themselves, and have them displayed side-by-side....and so forth.

 

Be creative. Have fun.