Mark Coppock/Digital Trends found in the Office suite, is the latest and greatest spreadsheet application from Redmond, and there’s a good reason so many number crunchers use it for all of their number crunching needs. While Excel is fine for simple spreadsheets to track expenses or build calendars, it comes into its own when you need to slice and dice and then. Here, we show you how to create a pivot table in Excel to take advantage of one of the application’s most powerful tools.Before we start, just what exactly are pivot tables good for? Simply put, pivot tables let you look at the same data in different ways and from different angles, to make it easier to perform in-depth analysis and to spot important trends. When you’re evaluating sales results, for example, you may want to look at an individual person, a specific product, or a specific timeframe.
![]()
With a pivot table, you can create one pool of information and then easily change your focus from one thing to another — an analysis that would be tedious to perform manually.Note that you’ll need the to use pivot tables. The Universal Windows Platform (UWP) doesn’t include that and many other. Finally, if you want to try out new Excel features, join the and perhaps be the first to experience a new pivot table function.
Step 1: Prepare your dataPerhaps the most important step in using Excel pivot tables is to carefully organize your data. The easiest way to do this is to use, which let you add rows that will be included with your pivot table whenever you hit refresh. But at the very least, you want your data to be in tabular form with informative column headers and with consistent data within columns.For example, let’s say that you want to analyze sales information for your company. You have six salespeople who sell three products across a number of regions. Your products are tailored for each customer and so pricing varies. Here’s a sample table with fictional information to give you an idea of how data can be organized with a pivot table in mind.The table was created simply by first entering the data, then selecting the entire range, and then going to Insert Table. Again, you don’t have to take this step but it’s recommended if you want to add more rows of data later and make it easier to update your pivot table.
Mark Coppock/Digital Trends. Mark Coppock/Digital Trends Step 2: Try a recommendationExcel is full of nifty tricks to make working with data easier, and whenever possible it will try to guess what you want to accomplish and then automatically carry out a few steps. This helpful nature is demonstrated here by the Recommended PivotTables tool, which takes a look at your data and offers up some logical options on how to analyze and present things.To use a recommended pivot table, simply go to Insert Recommended PivotTables. Excel will present a few options for you to consider. In our example, Excel offers to create 10 different pivot tables that take a look at a number of different angles on our sales data. Note that how you label your columns matters; Excel reads these headers and offers up recommendations that make the most sense. If you want to look at sales prices, for example, don’t use the term “cost,” because Excel will base its recommendation accordingly.One recommendation is “Sum of Total Sales by Customer.” If we choose that option, then Excel will proceed to create the pivot table.
Set PivotTable default options, so your PivotTables start the way you want every time. At the top or bottom of each PivotTable group, or don't display subtotals at all. Report Layout - Pick from Compact, Outline or Tabular report layout. Pivot Table AutoFormat XL allows you to save pivot table formatting and to use it as a format template for your future pivot tables. Pivot Table AutoFormat XL remembers formatting of each pivot table area: - Page fields area - Row fields area, including subtotals, blank lines and first rows - Column fields area, including subtotals - Data fields area - Common pivot table areas, including row.
![]()
Mark Coppock/Digital Trends. Mark Coppock/Digital TrendsNotice that the pivot table is displaying only the data that’s pertinent to our present analysis. On the right-hand side, you’ll find the criteria that Excel used to create it in the PivotTable Fields dialog. We’ll cover what each of these field means in the next section on customization. Step 3: Customize your pivot tableEach of the items in this dialog is important to determine how your pivot table will work. Click the configuration cog to alter this dialog’s look to whatever works best for you.Fields selectionHere, you are choosing which columns to include in your pivot table. How that data will populate in the pivot table is determined by the type of data that it represents — Excel will figure out for you whether to add a column to your pivot table or add the field’s data within the table. For example, if you select “Items,” Excel assumes you want to add the number of items for each customer.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |