How to Create a Pivot Table in Excel Online
Spreadsheets are great for cataloging large pools of data, but it takes a feature like pivot tables to really draw conclusions from the data. Excel Online makes it easy to create pivot tables that will help you summarize your data and give you more insight into what your raw numbers mean.
The pivot table is one of Microsoft Excel's most powerful -- and intimidating -- functions. Powerful because it can help you summarize and make sense of large data sets. Intimidating because you're not exactly an Excel expert, and pivot tables have always had a reputation for being complicated.
The good news: Learning how to create a pivot table in Excel is much easier than you might've been led to believe.
In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data together in different ways so you can draw helpful conclusions more easily.
While sometimes when spreadsheets start expanding beyond a few rows and columns, it becomes difficult to keep track of the data. And when you lose track of the data, you lose track of the meaning behind it. That's where a pivot table comes in—it filters and summarizes your data based on criteria of your choosing.
The "pivot" part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table in order to view it from a different perspective. To be clear, you're not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you're simply reorganizing the data so you can reveal useful information from it. Here are some hypothetical scenarios where you'd want to use a pivot table.
How to Create a Pivot Table in Excel Online
Here's a quick overview of how to use pivot tables (we'll dive deeper in the next section).
Step 1: Open the Excel Online sheet and select all cells containing the data you want to look at.
Step 2: Select Insert > PivotTable.
Step 3: From the pop-up, select New Worksheet and click OK.
Step 4: In the pivot table editor, drag the rows and columns that you want to summarize to the appropriate box.
Step 5: In the Values section, select the fields that have the values you want to add or calculate.
Step 6: If you only want to display values that meet certain criteria, use the Filters section.
How to Use Pivot Tables
1. Compare sales totals of different products.
Say you have a worksheet that contains monthly sales data for three different products -- product 1, product 2, and product 3 -- and you want to figure out which of the three has been bringing in the most bucks. You could, of course, look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears. You could then do the same for product 2, and product 3, until you have totals for all of them. Piece of cake, right?
Now, imagine that monthly sales worksheet of yours has thousands and thousands of rows. Manually sorting through them all could take a lifetime. Using a pivot table, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 -- and calculate their respective sums -- in less than a minute.
2. Show product sales as percentages of total sales.
Pivot tables naturally show the totals of each row or column when you create it. But that's not the only figure you can automatically produce.
Let's say you entered quarterly sales numbers for three separate products into an Excel sheet and turned this data into a pivot table. The table would automatically give you three totals at the bottom of each column -- having added up each product's quarterly sales. But what if you wanted to find the percentage these product sales contributed of all company sales, rather than just those products' sales totals?
With a pivot table, you can configure each column to give you the column's percentage of all three column totals, instead of just the column total. If three product sales totaled $200,000 in sales, for example, and the first product made $45,000, you can edit a pivot table to instead say this product contributed 22.5% of all company sales.
To show product sales as percentages of total sales in a pivot table, simply right-click the cell carrying a sales total and select "Show Values As" > "% of Grand Total."
3. Combine duplicate data.
In this scenario, you've just completed a blog redesign and had to update a bunch of URLs. Unfortunately, your blog reporting software didn't handle it very well, and ended up splitting the "view" metrics for single posts between two different URLs. So in your spreadsheet, you have two separate instances of each individual blog post. In order to get accurate data, you need to combine the view totals for each of these duplicates.
That's where the pivot table comes into play. Instead of having to manually search for and combine all the metrics from the duplicates, you can summarize your data (via pivot table) by blog post title, and voilΓ : the view metrics from those duplicate posts will be aggregated automatically.
4. Get an employee head count for separate departments.
Pivot tables are helpful for automatically calculating things that you can't easily find in a basic Excel table. One of those things is counting rows that all have something in common.
If you have a list of employees in an Excel sheet, for instance, and next to the employees' names are the respective departments they belong to, you can create a pivot table from this data that shows you each department name and the number of employees that belong to those departments. The pivot table effectively eliminates your task of sorting the Excel sheet by department name and counting each row manually.
5. Add default values to empty cells.
Not every dataset you enter into Excel will populate every cell. If you're waiting for new data to come in before entering it into Excel, you might have lots of empty cells that look confusing or need further explaining when showing this data to your manager. That's where pivot tables come in.
You can easily customize a pivot table to fill empty cells with a default value, such as $0, or TBD (for "to be determined"). For large tables of data, being able to tag these cells quickly is a useful feature when many people are reviewing the same sheet.
To automatically format the empty cells of your pivot table, right-click your table and click "PivotTable Options." In the window that appears, check the box labeled "Empty Cells As" and enter what you'd like displayed when a cell has no other value.
Digging Deeper With Pivot Tables
You've now learned the basics of pivot table creation in Excel. But depending on what you need your pivot table for, you might not be done.
For example, you may notice that the data in your pivot table isn't sorted the way you'd like. If were the case, Excel's Sort function can help you out. Alternatively, you may need to incorporate data from another source into your reporting, in which case the VLOOKUP function could come in handy.
To take a deeper dive into the world of Excel and learn about its various functions, download our comprehensive guide, How to Use Excel.
Want more Excel tips? Check out these design tips for creating charts and graphs.
Don't forget to share this post! and tell us in the comment section what you learned from this Blogpost.
πΏπππππ & π½ππππππ...
0 comments:
Post a Comment