What’s the Simplest Way to Create a Pivot Table in Excel if I’m Not a Math or Numbers Person?

If someone says “pivot table” do you break into a cold sweat and your hands get clammy??? You are not alone.  The good news is with Microsoft Excel 365, creating and working with pivot tables is nowhere near as hard or scary as it used to be.  In fact, it can be one of the fastest and simplest ways to truly make some sense of your data without needing to be a math guru!  Let’s work through it together.

What is a Pivot Table?

A pivot table is simply a table that summarises a big list of data and presents it in a visual dashboard format which is user friendly without the need-to-know complicated formulas or functions, just a few clicks. 

1. Start with Clean Data

Often data is exported from other programs into Excel and people expect it to be perfect.  Reality is, we can’t control how the data arrives in Excel, we can only control what happens after we open it in Excel.

• Firstly, open your Excel file and ensure your data is in a simple format.  This means, column headings across the top and rows of data directly underneath. 
• Remove any blank rows and unmerge any merged cells.  Excel LOVES order to make your pivot tables easy to create.  Follow the rules and Excel will be your new BEST FRIEND!

2. Insert the Pivot Table

• Click the Insert tab on the Ribbon
• Choose Recommended Pivot Tables – this is fantastic if you have no idea what a pivot table is or how to create one!
• A dialog box will pop up and a flashing line should be seen around your data. If you aren’t sure if all the data is selected, scroll down the worksheet to check.
• Excel will offer a range of Recommended Pivot Tables.
• Scroll through the various options until you find one that delivers the result you are after.
• Click OK.

Want to see it in action? Check out this quick video below.

3. Understand the Pivot Table

On the right side of the screen a Pivot table fields pane will display and Excel will have automatically placed elements of your data set ie column headings and repetitive row entries into rows, values or columns.

To rearrange the sequence:

• Drag and drop any of the entries between fields, columns, rows or values to see the pivot table on the left alter.

4. Format for Clarity

• Change the number formats on your pivot table by right clicking on an entry and choosing Number format.  Select the desired layout, ie currency, red for negative values etc.
• To clean or band the rows for easy viewing, click the Design tab and choose an option.
• Keep your spreadsheets clean and simple, making them easy to read and manage.

5. When Data Changes, Refresh It

• When you add additional information to your initial data list, the Pivot table needs to be refreshed to update the changes.  Either right click on the pivot table and choose Refresh or click the Refresh button on the Pivot Table Analyze tab.

6. Keep It Simple (KIS)

You don’t need to be a math genius or know complicated formulas or functions. Pivot tables are an easy way to drag and drop to create summaries.  The hard work lies in making sure the initial data has simple column headings and neatly organised rows of data.  Consider Pivot Tables your “cheat code” in Excel…..I won’t tell if you don’t!

Donna’s Final Thoughts

If you’d like more useful, practical and business specific Microsoft 365 productivity shortcuts that save time, stress and frustration whilst providing clarity and insight, my Productivity Hub is designed just for people like you.  It’s about making Microsoft 365’s core four programs less stressful and easier so you can get your work done quickly and keep up to date with changes in Microsoft 365.

Check out the hub here

Register for my next FREE Microsoft 365 Webinar – find out more and register here.