• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Online PivotTables

You are here: Home / Excel PivotTables / Excel Online PivotTables
excel online pivottables
March 14, 2018 by Mynda Treacy

Inserting PivotTables in Excel Online is now possible. It’s still in its infancy with many features you may be used to in the Desktop version of Excel not yet available, but it’s a start.

Inserting Excel Online PivotTables

As with any PivotTable, you need to begin with some Tabular Data. I recommend storing your data in an Excel Table so that the PivotTable can automatically pick up any new data upon refresh.

Note: Currently there’s no way to edit the source data range for your PivotTable from within Excel Online so it’s best to use Excel Tables as your PivotTable source data. If you need to edit the source data then you can open the file in the Desktop version of Excel.

Step 1 – Select your data or any cell in your table

Step 2 – Insert tab

Step 3 – PivotTable

Excel Online PivotTables

Step 4 – At the ‘Create PivotTable’ dialog you can edit the Table/Range detected by Excel and choose where you want to insert the PivotTable:

create pivot table

Note: Data sources are limited to data stored in the current workbook.

Step 5 – The PivotTable place holder is inserted in the worksheet grid and the Field List appears in the right-hand task pane:

insert pivottable placeholder

So far it looks just like PivotTables in Desktop Excel that you might be familiar with. Notice the column headers are listed in the ‘Field List’.

Place the fields in the area wells (Filters, Columns, Rows, Values); left click and drag them into place.

Hint: If you want to live on the edge just check the box for the fields and let Excel decide where to place them!

Or if you’re working on a touch screen you can click the drop down beside the field (press a field name to activate the drop-down button) and select from the menu:

pivottable fields 1

And voila, one Excel Online PivotTable:

completed excel online Pivottables

Excel Online PivotTable Tools

As I mentioned at the beginning, inserting PivotTables in Excel Online is brand new and so they don’t have the full functionality you may be used to from the Excel Desktop application. Let’s take a look at what we do have available.

Field Drop Down Menu

Clicking on a field drop-down arrow (shown below) is consistent with the Desktop version of Excel. The options differ depending on the area the field is in, with fields in the Values area also having a ‘Value Field Settings’ option:

field drop down menu

Change Aggregation Type

If you want to change a field in the Values area to Average, Count, Min or Max etc., simply click the down arrow beside the field in the field list area (image above) and select ‘Value Field Settings’. Here you can choose a different aggregation type:

change aggregation type

Tip: You can also give the column/row label a different name in the ‘Custom Name’ field in this dialog box.

Format Values Area

Number formats are also accessed from the Value Fields Settings menu > Number Format button. This opens the Number Format dialog shown below:

format values area

Tip: Using this dialog to set the number format ensures that as the PivotTable grows with new data those new values are formatted correctly, irrespective of any cell formatting applied, or not applied on the grid.

Show Values As

Let’s say you want to see a second column of values that displays the percentage of the total, as shown in column L below:

show values as

It’s easy done in Excel Online PivotTables:

  1. Add a second instance of the ‘Value’ field to the Values area
  2. Click on the drop down beside the new field
  3. add value field to the values area

  4. Value Field Settings > in the dialog box that opens (shown below):
    • A. Give your field a new name
    • B. Click the ‘Show Values As’ tab
    • C. Choose the type of calculation you want. Options from here differ depending on the calculations chosen.

value field settings

Learn more about Show Values As here.

Refreshing Excel Online PivotTables

Refreshing PivotTables in Excel Online requires a right-click of the PivotTable > Refresh:

refreshing excel online pivottables

Notice there aren’t the usual plethora of options in the right-click menu, including no PivotTable Options. Hopefully more will appear soon.

Editing/Updating PivotTable Source Data Range

If you formatted your source data in an Excel Table then you don’t need to edit the source range when you add data to the source, as Tables automatically resize and the PivotTable will include this new data upon refresh.

If you need to edit the PivotTable source data range, you can open the file in Desktop Excel and access the full PivotTable functionality there.

Learn PivotTables

PivotTables are a must have skill for any budding intermediate to advanced Excel user. Building reports with PivotTables can be done in a fraction of the time it takes to build the equivalent report using formulas. Plus, PivotTables can’t be broken, unlike formulas.

Take a moment to check out our PivotTable course.

Well, that’s it for this post. I hope you’ll find inserting PivotTables in Excel Online useful. This was one of the most requested features in Excel UserVoice and the Excel team have listened and delivered. If you have an idea for Excel, please take a moment to post it on UserVoice and get your colleagues to vote for it too.

Please Share

If you liked this please click the buttons below to share.

email icon twittericon fb icon LI icon
excel online pivottables

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.

Excel PivotTable Percentage Change

Excel PivotTable Percentage Change calculation is dead easy with Show Values As. Add conditional formatting, and Slicers for interactivity.


Category: Excel PivotTables
Previous Post:autosumAutoSUM Shortcut in Excel
Next Post:Excel ADDRESS Function

Reader Interactions

Comments

  1. Syndi Martin

    October 5, 2022 at 6:07 am

    I sure hope you can help me. I am trying to set up a pivot table in Excel Online, and i don’t get the same screens as you show. In step 5, instead of getting the setup screen you show, i get one that shows me 10 different suggestions for pivot tables (none of which i want) to select from and nothing else. Its like I either have to use their suggestion, or nothing.

    Reply
    • Mynda Treacy

      October 5, 2022 at 8:31 am

      Hi Syndi,

      The very first option in the new right-hand pane is ‘Create your own PivotTable’ with buttons to ‘Insert on a new sheet or existing sheet’. Choose one of those and then you should see Step 5 options.

      Mynda

      Reply
  2. JeteMc

    November 10, 2019 at 2:34 am

    Thank You!!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.