Simple Tricks in Using Pivot Tables
Microsoft Excel is tagged as one of the most used and highly efficient tools used in most companies and businesses. In fact, most establishments these days have now set their standards requiring applicants to be knowledgeable in Microsoft Excel. This doesn’t involve basic Excel skills, since most people already have a general idea on how to use this productivity tool. What companies need are individuals who are familiar with intermediate and advanced Excel features including the use of pivot tables.
The use of these tables may generally vary depending on the business’ needs. Utilizing the functions of pivot tables and other Excel features are easy as there are different tutorials and classes found online. Although there are numerous techniques and strategies that you can use to make the most out of Microsoft Excel, here are two simple yet very helpful strategy that you can make use of.
- Sorting Unique Values in a Spreadsheet
The most basic columns to use in this technique are date, product, customer, and quantity. The first and the most important thing to remember when working on pivot tables is you should have your spreadsheet ready before anything else. You can manage your spreadsheet and you can create these tables from the menu bar by choosing pivot tables. From there, you can highlight the cells and its corresponding values to use every time you’re about to manipulate and make use of this powerful productivity tool.
Upon completing the data fields and other important cells, go to data -> filter -> advanced filter command. If there are additional fields that you want to add, you may do so using this function. Apply the label by adding these as headers and highlight these new fields to be added on your new table.
On the PivotTable Field list, click customers and select add to row. This will give you the list of unique customers that you have, and this can be done in a matter of four simple clicks. Copy the cells to your worksheet, (a new or an existing one) and you already have your list of customers without repeating values.
- Calculate the Total Percentage
In the Products field on the other hand, if you want to compute the sum and the percentage of this value, you can go to data -> PivotTable & PivotChart Report. You’ll be given a blank field and choose the fields that you want to add to your spreadsheet. In this situation, you need to get the values as indicated on the products field, and choose that label. You also need to select the date field to track and compare the percentage of sales within a specified period of time. Select summarize by Sum and Show Data as % of row. After which, you’ll be presented with the summarized percentage report of all the products sold within a specific date.
Using pivot tables in your business makes complicated things easier. Study and find out other functionalities of Microsoft Excel including these pivottables and make your job less complicated.
