Microsoft Excel: Getting Started With Pivot Tables
Without question, one of Microsoft Excel’s best features is pivot tables. Pivot tables make the analysis, storage, and comprehension of data much easier, allowing you to better understand your business. In this video, we discuss how to get started using pivot tables in your workplace.
Understanding Pivot Tables
Pivot tables allow you to analyze and organize statistics by examining differences, similarities, highs, and lows in datasets. Before creating a pivot table, here are some things you should know. The data the pivot table is based on is called “source data''. The four different areas of a pivot table are “row labels”, “values”, “column labels”, and the “report filter”. Also, each column in a pivot table represents a different category of data, which helps improve organization..
How To Prep Your Data
Data must be prepared before going into a pivot table. Start by organizing it into columns and rows, with no blank areas except for cells. Similar data should be placed in the same columns. Format column headings differently than your data by bolding or centering the column heading. This helps the system differentiate between the two. Also, you should separate unnecessary information from the data by creating a data island.
How To Create A Pivot Table
To create a pivot table, go to “insert”, either “recommended pivot tables” or “pivot table”, confirm the range you are using, hit “new worksheet” and then “ok”. Continue building it by going to “pivot table fields” and choosing the fields to include. Hover over a cell, right-click, select “number format”, and choose a category to select which values are shown. You can also change how data is represented here.
More Helpful Tips
The tabs “pivot table analyze” and “design” will be shown when a table is open. To refresh your data, go to “pivot table analyze”. Refreshing is not automatic, so make sure to do it often, otherwise your information will not be up to date. To refresh, go to “pivot table analyze” and select “refresh” or use the shortcut Alt + F5. To refresh multiple tables, use “refresh all”. The range can also be updated by going to “change data source”. To create a new table out of a specific value, double-click on the value you would like to use, and a new table will be started.
Excel will create groups when multiple values are present. Next to each group, there will be the “-” option. Clicking this button hides the details of the group from view. Selecting “collapse field” in the ribbon also does the same thing. Furthermore, the display of the totals, as well as the overall appearance of the table, can be changed under “design”.
Use the column or row dropdowns to filter through a table. Once you are in the dropdown, select the categories you would like to see, and all others will be hidden from view. You can also filter by right-clicking on a value and selecting it. This will exclude all unselected values. Values that were not included in the table can still be used to filter as well.
How Will Using Pivot Tables Help You?
Pivot tables can make work in Microsoft Excel much easier and more streamlined. If you would like more information on pivot tables or Excel, reach out to us. At Velocity IT, we want you to have the tools you need to succeed. Contact us by phone or online today.