8.2 Create Pivot Table in Excel in Just 3 Clicks!, by Pavan Lalwani on YouTube. 13 min 46 s.
- Video length
- 13 min 46 s
- Time to do it
- 10 minutes
- Difficulty
- Easy
- Steps
- 10
What you need
Steps
Open your sales sheet and check that row 1 holds clear headers such as Date, Country, City, Product, Manager and Sales, with no blank rows or merged cells below.
Click any cell inside the data, go to the Insert tab and click PivotTable. Excel selects the whole range for you.
Choose New Worksheet in the dialog and click OK. A blank pivot table appears on the left with the field list on the right.
Drag Country into the Rows area and Sales into the Values area. Excel sums sales for each country straight away.
Right-click a sales figure, pick Sort and choose Largest to Smallest to see your best and worst countries first.
Remove Country from Rows and drop City or Product in its place to get that breakdown in seconds.
Add a second field under Rows, for example Product below Country, to compare products inside each country.
Select the value cells, open Conditional Formatting on the Home tab and use Top/Bottom Rules to highlight the top 3 or bottom 3 results.
Drag the Date field into Rows and Excel groups it into Years, Quarters and Months. Right-click a date and use Group to change the levels.
Replace the row field with Manager to get a manager-wise sales breakdown, then save the workbook.
Tips and common mistakes
Right-click the pivot table and choose Refresh after you change the source data, or the numbers stay old.
Use the Values field settings to switch from Sum to Count or Average when you are counting orders rather than adding sales.
Keep the source data on one clean sheet with a single header row so Excel reads every column correctly.
Format the value cells as currency or numbers so large sales figures stay readable.
Questions
Does a pivot table update when I add new rows?
Not by itself. Right-click the pivot table and click Refresh, or press Alt+F5 on Windows, to pull in new data.
Can I build more than one pivot table from the same data?
Yes. Repeat Insert, PivotTable on the same range and place each one on a separate sheet for country, product or manager reports.
Why are my dates showing as one long list instead of months?
Excel groups dates automatically in most versions. If it does not, right-click a date in the pivot table, choose Group and tick Months, Quarters and Years.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
