[SOLVED]How to list values from the same source and separate them by year

%3CLINGO-SUB%20id%3D%22lingo-sub-3195576%22%20slang%3D%22en-US%22%3EHow%20to%20list%20values%20from%20the%20same%20source%20and%20separate%20them%20by%20year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195576%22%20slang%3D%22en-US%22%3E%3CP%20style%3D%22%20text-align%20%3A%20left%3B%20%22%3EHello%2C%3C%2FP%3E%3CP%20style%3D%22%20text-align%20%3A%20left%3B%20%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20style%3D%22%20text-align%20%3A%20left%3B%20%22%3ESo%20I'm%20trying%20to%20%3CSTRONG%3E%3CU%3Elist%3C%2FU%3E%3C%2FSTRONG%3E%20mean%20values%20for%20two%20different%20sources%20and%20separate%20them%20by%20year%20to%20make%20a%20graph.%3C%2FP%3E%3CP%20style%3D%22%20text-align%20%3A%20left%3B%20%22%3E%3CBR%20%2F%3EThis%20is%20what%20it%20looks%20like%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3ESource%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EYear%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EMean%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2015%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2015%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2014%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EY%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2014%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%20style%3D%22%20text-align%20%3A%20left%3B%20%22%3E(years%20continue%20deceasing)%3C%2FP%3E%3CP%20style%3D%22%20text-align%20%3A%20left%3B%20%22%3E%3CBR%20%2F%3Eand%20this%20is%20what%20I%20want%20it%20to%20look%20like%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EYear%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3EY%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2015%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2014%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E2013%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E9%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%3E4%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESay%20I%20had%20mean%20values%20going%20back%20a%20thousand%20years%2C%20is%20there%20a%20way%20to%20quickly%20group%20them%20all%20together%20and%20list%20them%20like%20this%3F%20The%20layout%20is%20the%20same%20as%20the%20first%20table%2C%20always%20%22X%22%20then%20%22Y%22%20and%20the%20names%20never%20change%20either%20always%20%22X%22%20or%20%22Y%22.%20(If%20I%20have%20to%20do%20this%20one%20cell%20at%20a%20time%20then%20no%20big%20deal%20but%20I%20figured%20I'd%20try%20saving%20myself%20some%20time%20and%20learn%20something%20new)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3195576%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3195612%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20list%20values%20from%20the%20same%20source%20and%20separate%20them%20by%20year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313104%22%20target%3D%22_blank%22%3E%40DanielC98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20a%20pivot%20table%3A%3C%2FP%3E%3CP%3ESource%20in%20columns%20area.%3C%2FP%3E%3CP%3EYear%20in%20rows%20area.%3C%2FP%3E%3CP%3EMean%20in%20values%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3195617%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20list%20values%20from%20the%20same%20source%20and%20separate%20them%20by%20year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195617%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313104%22%20target%3D%22_blank%22%3E%40DanielC98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20create%20a%20pivot%20table%20based%20on%20the%20table.%3C%2FP%3E%0A%3CP%3EAdd%20Source%20to%20the%20Columns%20area%2C%20Year%20to%20the%20Rows%20area%20and%20Mean%20to%20the%20Values%20area.%3C%2FP%3E%0A%3CP%3EYou%20can%20turn%20off%20the%20Grand%20Totals%20of%20the%20Pivot%20table%20if%20you%20like.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1164.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350125i1C4FAB97DC3135F1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S1164.png%22%20alt%3D%22S1164.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3195622%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20list%20values%20from%20the%20same%20source%20and%20separate%20them%20by%20year%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195622%22%20slang%3D%22en-US%22%3EYes%2C%20thank%20you%20Hans%20and%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%2C%20you%20both%20answered%20my%20question%20perfectly.%3CBR%20%2F%3EYes%20how%20do%20I%20turn%20off%20Grand%20Totals%3F%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

So I'm trying to list mean values for two different sources and separate them by year to make a graph.


This is what it looks like:

SourceYearMean
X20156
Y20157
X20143
Y20145

(years continue deceasing)


and this is what I want it to look like:

YearXY
201567
201435
201394

 

Say I had mean values going back a thousand years, is there a way to quickly group them all together and list them like this? The layout is the same as the first table, always "X" then "Y" and the names never change either always "X" or "Y". (If I have to do this one cell at a time then no big deal but I figured I'd try saving myself some time and learn something new)

 

Thanks!

5 Replies

@DanielC98 

Create a pivot table:

Source in columns area.

Year in rows area.

Mean in values area.

 

@DanielC98 

You could create a pivot table based on the table.

Add Source to the Columns area, Year to the Rows area and Mean to the Values area.

You can turn off the Grand Totals of the Pivot table if you like.

 

S1164.png

Yes, thank you Hans and @Detlef Lewin, you both answered my question perfectly.
Yes how do I turn off Grand Totals?

@DanielC98 

Click anywhere in the pivot table.

Activate the Design tab of the ribbon.

Select Grand Totals > Off for Rows and Columns.

 

S1165.png

Beautiful, once again thank you, you taught me something new!