Pivot Tables are a great way to analyze and present data using Microsoft Excel. Excel Online has many of the same features as Excel for Desktop to create, edit and refresh pivot tables with a few exceptions. We’ll show you how to work with pivot tables in Excel Online and highlight some of the current limitations and available workarounds.
Excel Online has added tons of features over the last several years making it an inexpensive alternative to Excel for Desktop. We’ll look at Excel Online, creating pivot tables, formatting options, refreshing pivot tables and how to use the tool for more professional presentations and analysis.
Let’s jump in!
Table of Contents
How to Access Excel Online
Accessing Excel Online is straightforward, requiring only a Microsoft account and an internet connection. By visiting the Office.com website and logging in with your Microsoft credentials, you can easily navigate to Excel Online from the dashboard. This cloud-based version of Excel automatically saves and makes files accessible from any device.
Either click on the Apps button on the left side of the page, or use one of the app quick launch icons also on the left side of the page.
In addition to direct access through Office.com, Excel Online can also be launched after logging into many of the other Microsoft online applications, such Word, PowerPoint or Outlook by click on the top right corner that shows a matrix of dots.
The exact apps that appear will depend on which Microsoft 365 subscription level you have. Excel Online has many of the features of its desktop counterpart, giving users less and less reason to install the Excel desktop application.
What is an Excel Pivot Table?
An Excel Pivot Table is a powerful feature that allows users to summarize, analyze, sort, and reorganize large datasets, enabling quick insights and data-driven decisions. It provides a drag and drop interface to view rows and columns in a summarized view.
The following screenshot from Excel Online shows standard data on the left, and a pivot table summarizing the data on the right side. As the data on the left gets updated, you can refresh the pivot table on the right to re-calculate and preview the results.
This is an extremely basic example of a pivot table, you could drag and drop additional rows and columns or create different calculations for more advanced analysis.
Pivot Tables are essential for anyone dealing with extensive data sets. They give users a way to make sense of data tables that would otherwise be impossible to explore on a row-by-row basis.
How to Create a Pivot Table in Excel Online
To create a Pivot Table in Excel Online, start by selecting your data range and then navigate to the “Insert” tab and choose “Pivot Table.” Excel Online will prompt you to choose where you want the Pivot Table to be placed, either in a new sheet or an existing one.
To Quickly select a table, click the middle of the table and press CTRL+A to select all.
We recommend thinking through how your data will be updated, if it’s a single table where there won’t be any additional rows added in the future selecting a table will work ok. If you expect to add rows in the future, you may want to select the full columns so you don’t have to update the data range of your Pivot Table in the future.
When you select Pivot Table, you will have the option to create one from a selected Table/Range or you could alternatively connect a Pivot Table to an existing Power BI dataset. Power BI is Microsoft’s business intelligence tool and has many advanced features that can come in handy, such as automatic data refreshes.
Select From Table/Range, and on the right side of the window a panel will appear that suggests starting points of possible pivot tables using AI.
The panel interface will look a bit jarring if you’re used to creating pivot tables in Excel for Desktop, but we suspect that the interfaces will be unified at some future point in time.
Click on the +New Sheet or + Existing Sheet to tell Excel where you would like the new Pivot Table to be created.
In our example, we’ll create a pivot table in an existing sheet. When you select this option, a new screen on the Pivot Table Panel will appear asking you which cell you want to create the Pivot Table in. Select a cell for the destination, this becomes the top left corner of the Pivot Table.
This generates a blank Pivot Table that we can add data to for summarization and analysis.
Once your Pivot Table is created, you can begin to drag and drop fields into the Pivot Table Fields pane, categorizing data into columns, rows, values, and filters. Experimenting with different data arrangements can unveil patterns and trends that might not be evident in a traditional spreadsheet layout.
Adding Fields to Excel Online Pivot Tables
After creating a new Pivot Table in Excel Online, you can select it and a panel of options will appear on the right side of the screen. At the very far right there are small icons that let you switch between Pivot Table Fields, and Pivot Table Options. Start with Pivot Table Fields.
Drag and Drop column names from the left side of Pivot Table fields and place the fields into Filters, Rows, Columns or Values. Excel will automatically format the data and summarize it to give you a view of your data that can be quickly analyzed.
The fields are contextually aware, meaning that if you have a date field you can add it to columns and Excel will automatically group the data by year, month, data letting you drill into it at different levels.
Experiment around with adding different columns, rows, values, and filters to get a hang of how the different interactions work with your own dataset. You can add multiple fields to a single row, column or value to let Excel automatically group and nest the data making it an incredibly flexible and powerful feature!
Removing Fields from Excel Online Pivot Tables
To remove fields from a pivot table, select the pivot table and then navigate to the Pivot Table Fields panel. Either click on the down arrow of an assigned field and select Remove Field or hold down the left-mouse button and drag the assigned field back to the list of available fields on the left side of the panel.
Pivot Tables are extremely drag and drop friendly, letting you move fields up and down within an assigned field, quickly re-calculating based on the order of the assigned fields. Pivot Tables are designed for quick interactions and data exploration.
Excel Online Formatting Options
Excel Online offers a range of formatting options to enhance the visual appeal and readability of your data. From adjusting cell styles, font sizes, and colors to applying conditional formatting rules, these tools allow for a customized data presentation.
Formatting options in Excel Online are located on the far-right Pivot Table Settings Panel which is different from Excel for Desktop which keeps most formatting options on the top Excel Ribbon under the PivotTable section.
Many of the most common formatting options can also be accessed by right-clicking on the pivot table itself. Microsoft has been moving towards providing better on-object interactions, meaning that you can change settings from the pivot table itself to avoid navigating to a separate Ribbon or Panel to adjust settings.
We like the added ability to make changes directly from a visual, but often forget that it’s possible after having used the traditional Excel for Desktop interfaces for so long.
Formatting options are fairly comprehensive, including the ability to add blank lines, automatically refresh the pivot table upon opening of the Excel workbook while some coloring options such as alternating row color are still made under the PivotTable Styles settings at the top of the Excel Online interface.
Creating Calculated Fields in Excel Online Pivot Tables
To add calculated fields in an Excel Pivot Table, assign a field to pivot table values, then click the arrow on the right side of the field name and select Value Field Settings. A Value Field Settings Dialog box will appear allowing you to select different calculation types and determine how you want the value to appear.
The following example is from the Show Value As section of the Pivot Table Value Field Settings. This section includes most of the common calculations that are performed in a Pivot Table, but there are some limitations compared to the Excel for Desktop application.
Keep in mind that you can add the same field multiple times to the Values section of a Pivot Table. This way you can view the individual values, and calculate % of a total row for example.
Repeating Pivot Table Rows in Excel Online
Creating a Tabular View in an Excel Online Pivot Table isn’t available at this time. There is a formatting option that allows you to place fields from rows on separate columns, and repeat item labels, but there is no clear way to disable category nesting.
Formatting options are available under the PivotTable Settings Panel, and hopefully new features are added in the future. For now, you can work around this limitation by copying the Pivot Table, pasting as values, and filtering out blank rows. It will turn the pivot table into a static table, meaning that you would have to copy and paste it every time you wanted to remove the category labels at the top.
Adjusting Pivot Table Themes
Excel Online enables users to adjust pivot table themes, offering a variety of styles that can be applied to enhance the visual presentation of your data. Through the “Design” tab under PivotTable section of the Excel Ribbon, you can select from a range of themes that change the color, font, and border of your pivot table.
Use the PivotTable Styles section to add or disable alternating row colors on a pivot table for improved readability.
When relevant, try putting your pivot tables into a color scheme that echos your company’s brand colors. It’s a subtle change that lends your presentation additional credibility and improves readability by your audience.
Refreshing Pivot Tables in Excel Online
Refreshing Pivot Tables in Excel Online can be completed in a number of different ways. Refresh a Pivot Table by right-clicking on the pivot table and selecting “Refresh,” by using the “Refresh” button under the “PivotTable” tab, or enable Refresh Data on File Open under the Pivot Table Settings Panel.
Keeping your pivot tables refreshed is essential for maintaining the integrity of your data analysis, particularly in dynamic environments where data is constantly evolving.
Pivot Tables are great for analysis, and they become even more powerful when you connect them to live data sources, such as Power BI Datasets.
Excel Online has come a long way since it was introduced and represents a credible low-cost alternative to Excel for Desktop. As features are added and the platform evolves, we expect that the interface from Excel Online will make it to the desktop app and a number of features from Excel for Desktop will be added to Excel Online.
Pivot Tables can be created from tables of existing data or by connecting to a Power BI Dataset. Select your data and navigate to “Insert” and select “Pivot Table” from the Excel Ribbon. A new Pivot Table can be created in an existing sheet or new sheet. Drag and drop fields from the Pivot Table Field panel into rows, columns, filters, or values. Click on an assigned field to change the calculation method or remove it entirely.
Formatting pivot tables in Excel Online can be performed by right clicking the pivot table or selecting the pivot table and navigating to the pivot table formatting settings panel.