When using structured data, there are some neat ways to seamlessly create powerful and interactive views for your stakeholders. In this video, learn how to create engaging Excel Online PivotTables and PivotCharts.
- [Narrator] Pivot tables and pivot charts are a super fun and engaging way of getting the most from your Excel online data. However, in my experience, many users of Excel find them rather daunting, but this doesn't need to be the case. So, let me break this down into some easy to follow steps for you. Firstly, make sure you have this exercise file open XOL Pivot. What you're looking at is a very simple table of structured data. that is data that Is organized into rows and columns. To ensure that pivot tables and pivot charts work correctly, it's really important that your data is organized and structured such as this one here. Okay. Let's go ahead and select the table including the column headings. Now let's go ahead and click on Insert at the very top of the screen and then select Pivot Table. Now the table or range has been automatically selected. You can change it if you like. Sometimes I like to at a couple of zeros to the last row, that way if we add more data down the track, the pivot table will automatically include any new information that we have here. Okay. As a default, we're asked to include the pivot table on a new worksheet, or if you like, you can select the existing worksheet. Now in my experience selecting the existing worksheet is really risky because we're suddenly mixing out data with the reporting and the pivot table. It's a really good idea to keep the two separate. So I strongly suggest that you leave new worksheets selected and then click on OK. Now this might look a little bit scary, but we have the outline of a pivot table that has already been created. On the right hand side here, you can see that we have a list of the columns that we were looking at just before in the Pivot worksheet just here. Let's go back to Sheet1 and drag the general ledger code and drop it under rows. Now let's go ahead and drag the amount, which is full year actuals and drop it in the values section. And just like that we have quickly and quite easily created a pivot table using our data. Now, at this point, you may want to change the format of the numbers here. I found that the easiest way to do this is to select the entire column and under the number format here, select Comma Style, and then click on Decrease Decimal twice to reduce the decimals to zero decimal places. Now let's go and click again in the middle of our pivot table and they have it. We have a pivot table that's been created using the general ledger codes and the amounts. What this is essentially doing is adding up the total of the expense codes from the pivot worksheet. Let's go back to Sheet1. And lastly, say we want to create a two dimensional pie chart using this data. We don't need to copy this data and move it somewhere else with the pivot table selected, we can simply go ahead and click on Insert and under Charts, click on the dropdown box under Pie and select 2D Pie. In a split second, a 2D pivot chart has been created using the data that's in our pivot table. Now I'm sure you'll all agree that creating pivot tables and pivot charts in Excel online is both powerful and a breeze.
Note: This course is for users who know the basics of the Excel desktop app, but are new to the web version, Excel Online. It emphasizes features that work differently in Excel Online. For basic Excel guidance, please see Excel Essential Training (Office 365) or Learning Excel 2019.
- Using Excel Online shortcuts
- Customizing your view
- Integrating the desktop version of Excel
- Sharing workbooks in Excel Online and OneDrive
- Inserting and reviewing comments
- Creating and editing charts
- Working with PivotTables and PivotCharts