You are reading the article Excel Online Pivottables • My Online Training Hub updated in September 2023 on the website Lifecanntwaitvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Excel Online Pivottables • My Online Training Hub
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
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:
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:
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’.
Hint: If you want to live on the edge just check the box for the fields and let Excel decide where to place them!
And voila, one Excel Online PivotTable:
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
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
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:
It’s easy done in Excel Online PivotTables:
Add a second instance of the ‘Value’ field to the Values area
A. Give your field a new name
C. Choose the type of calculation you want. Options from here differ depending on the calculations chosen.
Learn more about Show Values As here.
Refreshing Excel Online PivotTables
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
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
You're reading Excel Online Pivottables • My Online Training Hub
Update the detailed information about Excel Online Pivottables • My Online Training Hub on the Lifecanntwaitvn.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!