Published on March 13, 2014
E X C E L C R A S H C O U R S E : P I V O T T A B L E S H O W T O I N T E R P R E T D A T A F A S T E R , E A S I E R & M O R E E F F I C I E N T L Y ! ! ! J E F F M A L C Z Y K A S S O C I A T E S E A R C H M A R K E T I N G S T R A T E G I S T
B U T W H A T I F Y O U C O U L D T A K E T H I S … SOMETIMES, SPREADSHEETS TEND TO LOOK LIKE THIS x 28 A N D T U R N I T I N T O T H I S ? x 14 x 12 x 3 x 1
SO WHAT IS A PIVOT TABLE? A Pivot Table is a Data Summarization Tool used to automatically sort & count data from thousands of rows & columns in seconds… ! …but it’s much more than that Turning This Into This
HOW DO PIVOT TABLES WORK? Using the genius, yet simple Pivot Table Builder, you can drag and drop ﬁelds to immediately reorganize, and more importantly, reinterpret the data in your spreadsheet.
WHY SHOULD YOU BE USING PIVOT TABLES? - Easily count how many times a group of values appear in a spreadsheet - Compare Performance between Ads and determine the most effective variation - Discover the optimal time of day and day of week to post on Facebook or Twitter - Show the month-over-month growth in website visitation and page performance - Calculate a sales Conversion Funnel using pageviews - Can ﬁt practically any situation involving data analysis
L E A R N I N G T H R O U G H E X P E R I E N C E PLEASE DON’T CRINGE Download and open the Excel Practice Sheet
FORMAT TING YOUR RAW DATA 1. Auto-Column Width 2. Format First Row 3. Freeze Panes 4. Quick-ﬁlter 5. Quick-Number-formatting 6. Inserting/Deleting Rows & Columns 7. The Status Bar
1. AUTO COLUMN WIDTH To change the width of a column, simply move the cursor to the right edge of the column you wish to format, click, drag, and release at the desired column width. ! - By double-clicking the edge, the column will automatically expand to an appropriate width. - You can Automatically adjust the size of all columns in the spreadsheet by selecting all cells (click the greyed out cell where the columns (A, B, C…) and rows (1, 2, 3…) intersect.) Once you have all cells selected, double-click any column edge. this will auto-adjust the width of every column in the spreadsheet. ! - If you want the columns to have a uniform length, Select All Cells and drag any column to the desired length. VIDEO EXAMPLES ARE ON THE SLIDE AFTER EACH STEP
2. FORMAT L ABEL ROW - To Select an entire column, move the cursor and click over a column label (A, B, C…). The Same can be done with Rows (1, 2, 3…) ! - You can change the formatting under the Home Tab in the formatting Box. In the Practice Spreadsheet, Change the Formatting of the First Row to make it stand out.
3. FREEZE PANES Freezing Panes can allow you to take a row, or number of rows and columns and freeze them so they’re always visible while scrolling through the rest of your spreadsheet. ! - You can Freeze Panes by going to ‘Window’ in the main toolbar, and clicking ‘Freeze Panes’ - When you do, Excel will freeze all of the panes to the left and top of your cell selector. ! - To Unfreeze Panes, go to ‘Window’ in the main toolbar and clicking ‘Unfreeze Panes’ ! - For the purposes of this spreadsheet, select the cell A2 and Freeze the Panes so only the ﬁrst row is frozen.
4. QUICK FILTER Quick Filtering allows you to apply simple-to-use Sorting & Filtering capabilities to your spreadsheet. ! - Select the First Row and click the Funnel Shaped ‘Quick Filter’ button in the top toolbar. Alternatively you can click ‘Data’ in the main toolbar and selecting ‘Filter’. From here, you can see every column label has a drop-down menu button next to them. From you you can sort the data Ascending, Descending, or even by cell color. You can even ﬁlter out speciﬁc data entries from that column.
5. QUICK NUMBER FORMAT TING Excel has a number of time-saving techniques for you to edit and format your spreadsheet to meet your speciﬁcations. One of these functions includes changing the number Formatting for your cells. ! - In the ‘Home’ tab, click the comma-shaped button underneath the section that says ‘Number’. This will change the number formatting to include ‘1,000-comma-indicators’ and by default, this also adds two decimal places. - You can change how many decimal places are shown by clicking the left and right arrow buttons next to the comma-shaped button. ! - Additional quick formats include currency & percentages.
6. INSERTING/DELETING COLUMNS & ROWS Sometimes, it will be necessary to add a column in- between two existing columns. ! - Simply select the column to the right of the desired location, right-click and select Insert. ! - Alternatively, you can go to ‘Insert’ in the main toolbar and select ‘Column’ (or row if you are adding a row). ! - The same process can be applied to Rows.
7. THE STATUS BAR Excel has a neat tool hidden in plain sight, occasionally missed by spreadsheet veterans. The Status Bar is located in the bottom of the window, underneath the horizontal scroll Bar. ! The Status bar has several simple functions, including Sum, Count, Average & more. The Status Bar will apply one of those functions to the selected group of cells. - For instance, If you select the entire column labeled ‘Impressions’, you can see the status bar at the bottom calculate the total sum of impressions for all selected Cells. ! Already, you can see how these couple tips and tricks can save you a lot of time trudging through spreadsheets. Let’s kick it to the next level, and see what these Pivot tables are all about.
YOUR FIRST PIVOT TABLE 1. Create your ﬁrst Pivot Table 2. Arrange ﬁelds & Values using the Builder 3. Pivot Table Field Options 4. Quick-Formatting for Pivot Tables 5. Refreshing the Pivot Table 6. Calculating Fields 7. Grouping Fields 8. Conditional Formatting 9. Advanced Field Options 10. Pivot Charts
1. CREATE YOUR FIRST PIVOT TABLE - Select Columns A through U ! - In the Main Toolbar, Click ‘Data’ and select ‘Pivot Table’ ! - A dialogue box will appear asking you where you would like to place the pivot table. The Default option of creating a new sheet for your pivot table will meet most of your uses. ! - Click ‘OK’
2. ARRANGE FIELDS & VALUES The Builder allows you to choose how you arrange your ﬁelds. ! - Click-and-drag the ‘Clicks’ Field into the ‘Values’ Box - Click-and-drag the ‘Target’ Field into the ‘Row Labels’ Box - Click-and-drag the ‘Campaign Period’ Field into the ‘Report Filter’ Box - Click-and-drag the ‘Campaign Messaging’ Field into the ‘Column Labels’ Box. - Now you can see how Pivot Tables populate the Data ! - At the top of the pivot table, click the ﬁlter drop- down-menu box and select only the ‘Nov to Dec’ entry.
3. PIVOT TABLE FIELD OPTIONS When you click away from the Pivot Table, the Builder will automatically hide itself. ! - To reactivate the Builder, simply click on the pivot table. Alternatively, click the purple ‘Pivot Table’ tab and select ‘Builder’. ! - You can change the function of the formula aggregating your data into the Pivot Table. - To do this, click on the round ‘i’ button next to the ‘Count of Clicks’ ﬁeld in the Values Box in the Builder. This will activate the Field Options dialogue box. - In the ‘Summarize by’ box, scroll up and select ‘Sum’ - Click the ‘Number’ button to change the number formatting of the selected ﬁeld. In the example, I selected ‘Accounting’ number formatting with ‘0’ decimal points and the currency symbol changed to ‘none’
4. QUICK-FORMAT TING - Excel has a number of Quick-Format settings for Pivot Tables. You can scroll through them in the Purple ‘Pivot Table’ tab under ‘Pivot Table Styles’. ! - Select a Pivot Table Style that suites you.
5. REFRESHING THE PIVOT TABLE Pivot Tables are not a live representation of your source data. If you ever add, remove, or edit rows or entries in your Raw Data tab, that change WILL NOT be reﬂected in the pivot table automatically. You ALWAYS need to manually refresh your pivot table anytime you make a change to that data. ! - To Refresh your data go to the Purple ‘Pivot Table’ Tab and click the Refresh Button, and select ‘Refresh All’
6. CALCUL ATING FIELDS Pivot Tables have the ability to calculate new ﬁelds not present in your original Raw Data File. In this example, we are going to add a ‘Click Through Rate (CTR)’ ﬁeld to our Pivot Table. ! - Select your Pivot Table and in the Main Toolbar, click ‘Insert’ and select ‘Calculated Field’. Type ‘Click Through Rate (CTR)’ into the Name box. - In the Fields list, select ‘Clicks’ and click ‘Insert Field’. - In the Formula, type ‘/‘ - In the Fields list, select ‘Impressions’ and click ‘Insert Field’ - The Formula Bar should look like this: Clicks / Impressions - Click ‘Add’ and ‘OK’ to return to the Pivot Table. - Using your prior knowledge, format the ‘Click Through Rate (CTR)’ ﬁeld to a Percentage.
7. GROUPING FIELDS You can group together certain types of data in pivot tables. For Instance, you can group dates to display Weeks, Months, or even Years as needed. ! - Click-and-drag the ‘Target’ Field back into the rest of the Fields list. Replace it with the ‘Date’ ﬁeld. ! - Click into the Dates on the Pivot Table, Right- Click and select ‘Group & Outline’ and then ‘Group…’ - In the ‘By’ list, select ‘Days’ and change the ‘Number of Days’ to 7 to represent a full week. !
8. CONDITIONAL FORMAT TING Conditional Formatting is like a Formula that changes the color, size and shape of the cells based on their values. In this example, we will be applying a 3-color scale to a range of Cells. ! - Select the range of Cells in column ‘D’ and in the Main Toolbar, select ‘Format’ and select ‘Conditional Formatting’. ! - To Add a new Rule, Click the ‘+’ button in the bottom Left Corner of the dialogue box. - Change the ‘Style’ to a ‘3-Color-Scale’. You can change the cell ﬁll here if you wish to. Click ‘OK’ in both dialogue boxes. ! - You can copy and paste conditional formatting using the copy formatting tool (it looks like a paintbrush in the excel toolbar)
9. ADVANCED FIELD OPTIONS Excel has a number of Advanced Field Options that can allow you to view the same data in a different light. In this Example, we will be comparing the volume of clicks to the previous week’s volume of clicks. ! - Remove the ‘CTR’ ﬁeld from the Values Box and click the ‘i’ button in the ‘Sum of Clicks’ Field to open the Pivot Table Field Dialogue Box. - Click the ‘Options>>’ button to reveal advanced options. - In the ‘Show Data As’ drop-down-list, select ‘% Difference From’ and in the ‘Base Item’ as ‘(previous)’. Click OK
10. PIVOT CHARTS Lastly, Pivot Tables have the ability to quickly create charts based on all of the data presented in the Pivot Table. ! - Simply select your pivot table, and in the ‘Charts’ tab, select Column, and then ‘Clustered Column’
LOOK AT YOU! ALL PRO
T H A N K Y O U F O R W A T C H I N G ! ! C O M I N G S U M M E R 2 0 1 4 E X C E L C R A S H C O U R S E P A R T D E U X : S N E A K Y , S N E A K Y F O R M U L A S
Canvas Prints at Affordable Prices make you smile.Visit http://www.shopcanvasprint...
30 Días en Bici en Gijón organiza un recorrido por los comercios históricos de la ...
Con el fin de conocer mejor el rol que juega internet en el proceso de compra en E...
With three established projects across the country and seven more in the pipeline,...
Retailing is not a rocket science, neither it's walk-in-the-park. In this presenta...
Welcome to Excel Crash Course! ... conditional formatting crash course excel basics Excel Charts excel table Functions pivot table. You may also like ...
or "Excel Pivot Table unexpectedly" is ... auto crash data using an Excel 2007 pivot table. ... in 2 days from now..is there a crash course for a ...
... 5 Excellent Crash Courses on Varied Features of ... - Microsoft Excel Dashboard Crash Course - Pivot Tables: Microsoft Excel's Instant ...
Pivot Table Week Session 1. What Are Pivot Tables? April 2, 2012 by Barbara Leave a Comment. ... Excel Formula Crash Course ...
Microsoft Excel Pivot Table training course presented by Chartered Accountants with over 15 ... Conditional Formatting on Pivot Tables (Excel 2007 and ...
Excel 2016 Pivot Tables. ... What does the course cover? Excel 2016 Pivot tables provide a ... Anyone using Excel unfamiliar with Pivot Tables who is ...
Pivot tables are one of Excel's most powerful features. A pivot table allows you to ... dimensional Pivot Table. Pivot tables are one of Excel's most ...
... first registration priority will go to the Pivot Table Crash Course ... whole new level of power in Excel. ... like Pivot Tables are too ...
5 Excellent Crash Courses on Varied Features of Microsoft Excel. ID: 3673976; January 2016; Region: Global; ... as well as recommended Pivot Tables in ...