Let\u2019s start with a basic question \u2013 what is this mysterious \u201cPivotTable\u201d thing?<\/p>\n\n\n\n
Microsoft\u2019s definition:<\/p>\n\n\n\n
A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.<\/em><\/p>\n\n\n\n That\u2019s a pretty solid definition, but if I had to make it more concise:<\/p>\n\n\n\n PivotTables let you organize your data into groups<\/u><\/em>.<\/p>\n\n\n\n This is easiest to demonstrate with an example:<\/p>\n\n\n\n Say we have some very simple product sales data, like this:<\/p>\n\n\n\n Download the file here:<\/p>\n\n\n\n <\/p>\n\n\n\n Just looking at the data, you can probably come up with a few different questions to investigate:<\/p>\n\n\n\n All of these can be answered in a straightforward way with PivotTables, by creating groups and then performing calculations (such as sum, average, etc.) across the groups.<\/p>\n\n\n\n Let\u2019s dive in:<\/p>\n\n\n\n Highlight your data (or Ctrl + A), go to the \u201cInsert\u201d tab on the ribbon and click \u201cPivotTable\u201d.<\/p>\n\n\n\n A dialog box will appear:<\/p>\n\n\n Since I\u2019ve formatted my data as a table, I can simply select the Table by its name (Table1 in this case).<\/p>\n\n\n\n I\u2019ll insert the PivotTable in a new sheet, but you can also put it in the same sheet if you\u2019d like.<\/p>\n\n\n\n Click \u201cOK\u201d, and you should get something like this:<\/p>\n\n\n\n So now we need to decide two things:<\/p>\n\n\n\n The groups will go in the \u201cRows\u201d and\/or \u201cColumns\u201d areas.<\/strong><\/p>\n\n\n\n 2. How we want to aggregate the data (sum, average, etc.)<\/p>\n\n\n\n The value we want to aggregate will go in \u201cValues\u201d.<\/strong><\/p>\n\n\n\n Let\u2019s start by breaking down the first question from earlier: How have Total Sales trended over the past few months?<\/p>\n\n\n\n So we know we\u2019re going to be measuring Total Sales<\/em>, so let\u2019s drag that into the \u201cValues\u201d area:<\/p>\n\n\n\n Nice! Notice that Excel defaulted to using \u201cSum\u201d for the aggregation, which is perfect in this case because we want to know Total Sales<\/em>.<\/p>\n\n\n\n So we\u2019ve already created our aggregation, now we need to break the sum down into groups.<\/p>\n\n\n\n We want to know sales over the last few months<\/em>, so this means we need to group the data by Order Date.<\/p>\n\n\n\n Let\u2019s drag Order Date into \u201cRows\u201d:<\/p>\n\n\n\n Awesome!<\/p>\n\n\n\n Notice how Excel automatically converted our date into a \u201cdate hierarchy\u201d (Month, Day, etc.). If you expand the \u2018+\u2019, you can see the days underneath the month.<\/p>\n\n\n\n Ok, so we answered our question, but let\u2019s make it easier to analyze by inserting a chart.<\/p>\n\n\n\n Under \u201cPivotTable Analyze\u201d on the toolbar, click \u201cPivotChart\u201d:<\/p>\n\n\n\n Since we want to see a trend over time, let’s choose a line chart.<\/p>\n\n\n\n Click “OK”:<\/p>\n\n\n Pretty cool!<\/p>\n\n\n\n Now we can see how our Sales data is trending over time.<\/p>\n\n\n\n That’s it for today, ’til next time! <\/p>\n","protected":false},"excerpt":{"rendered":" Hey everyone! Welcome to my first blog post ever \ud83d\ude0a Today I\u2019m going to dive into everyone\u2019s favorite Excel feature \u2013 PivotTables! If you\u2019re a beginning Excel user, don\u2019t be scared! I\u2019ll show you how PivotTables are a lot simpler than you probably think. What is a PivotTable? Let\u2019s start with a basic question \u2013… Read More »Pivot Tables \u2013 A How-To Guide<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","_themeisle_gutenberg_block_has_review":false,"_ti_tpc_template_sync":false,"_ti_tpc_template_id":"","footnotes":""},"categories":[1],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/posts\/213"}],"collection":[{"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/comments?post=213"}],"version-history":[{"count":3,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/posts\/213\/revisions"}],"predecessor-version":[{"id":284,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/posts\/213\/revisions\/284"}],"wp:attachment":[{"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/media?parent=213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/categories?post=213"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jayexcelblog.com\/index.php\/wp-json\/wp\/v2\/tags?post=213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Data-Image-1.png\")
<\/figure>\n\n\n\n\n
\n
\n
Step 1 \u2013 Inserting the PivotTable<\/u><\/strong><\/h3>\n\n\n\n
![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Insert-PT-Image-1.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Insert-PT-Image-2.png\")
<\/figure><\/div>\n\n\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Blank-PT-Image.png\"){kind=spacer}
<\/figure>\n\n\n\nStep 2 – Using the PivotTable to Group & Aggregate<\/span><\/h3>\n\n\n\n
\n
![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Total-Sales-PT-Image.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Total-Sales-by-Date-PT-Image-1.png\")
<\/figure>\n\n\n\nStep 3 – Inserting a PivotChart<\/span><\/h3>\n\n\n\n
![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/PivotChart-Image-1024x265.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/Line-Chart-Insert.png\")
<\/figure><\/div>\n\n\n![\"\"](\"https:\/\/jayexcelblog.com\/wp-content\/uploads\/2023\/10\/PivotChart-End-Result-1024x563.png\")
<\/figure>\n\n\n\n