Some of us have used Microsoft Excel for so many years, we have built habits to do things the “old way.” Some of us have been using it for only a few years but do not know some of the best ways to use it. As with any software from a large company, enhancements come fast, so let’s learn about some ways to use Excel more efficiently. Each of the items listed below is a habit I build for creating and manipulating Excel data in pivot tables.
- Instead of using the standard “paste” button, use the dropdown menu to choose the type of pasting you wish to do.
Many users of Excel grab some data from another source (the Web, a report, Word, etc.) and then just click “paste” in Excel to bring in the data. If you do this, you might be missing out on a cleaner way to bring in the data. When trying to analyze data, especially in a pivot table, my goal is to get it into Excel as cleanly as possible and then use the “format as table” feature. For example, I frequently copy a list from a website that contains a small thumbnail image and hyperlinks on each row. If I just copy and paste, it brings all of those items over. However, if I click on the dropdown arrow below the “paste” button, it will allow me to paste just the text and lose the rest. To accomplish, click on the arrow, choose “paste special,” and choose text. In addition, if you are copying from a source like another Excel sheet, you get many more paste options, including items like “transpose,” which turns a row into a column or vice versa.
- ID your table to ease sorting the data set.
Every time I receive a workbook with Excel data, I add a new ID and count “1, 2, 3,” etc. until each row has an ID. This ID column serves multiple purposes. I can easily re-sort my data back to its original layout. I can also use this ID column in a “count” formula on a pivot table. (A column can be used only one time on a pivot table, so once you use it on, say, the filter area, you can’t add the same column to the value area.)
- Use formatted Excel tables with pivot tables to quickly analyze data.
Here is a sample of a workbook I recently received, with hundreds of rows for the customer listing, columns for the sale amount and the facility it came from, and lastly, a column for where it was shipped to. I wanted to know the amount of sales by the shipped-to state, in addition to how many Wisconsin sales went to Illinois locations and how many Illinois sales went to Wisconsin locations. This was easily done in a pivot table. After quickly using the “format as table” feature on the data set, I could easily set up a pivot table. (Insert Pivot Table). I then set up three separate tabs, one for each use:
- Pivot by state
- Pivot for Wisconsin shipped to Illinois
- Pivot for Illinois shipped to Wisconsin
Here is an image of the data fields for how to configure your pivot table to quickly achieve your results for #3 above. I then selected “Illinois” on the dropdown box in the pivot table for the filter. To toggle the second item above, I could quickly change the value to “Sum of IL sales” and then change the shipped-to state to be Wisconsin.
- Instead of re-creating pivot tables, duplicate tabs to create multiple views of data.
Many times, I need multiple views of the same data set. Once I set up my first pivot table, I often just right-click the tab, choose “move or copy,” and choose “copy” to insert a duplicate into the existing workbook. I can then make the slight modification to see the data differently.
- Use “slicers” to give users of the workbook quick and easy ways to filter their data.
An option to quickly filter your data inside a pivot table is to use the “slicer” feature. I find this is best used to give another user a quick way to filter my pivot table to a narrower band of data. To explain the use of this feature, I will use my example from above. First, I click inside the pivot table, then I go to the “analyze” tab and select “insert slicer.” This allows me to then select which columns I want a slicer for. The slicer then puts a “pane” on each item selected for the data found inside that item. I can then click on one or many items to quickly filter and change the results of my pivot table data.
- Use Excel artificial intelligence (AI).
Well maybe not quite yet. But recently Microsoft spoke about Excel AI at the September 2017 Microsoft Ignite Conference. Microsoft will be adding formulas [i.e., =AZUREML( )] to quickly see a catalog of AI functions that data scientists are using in their organizations. You can read more about this upcoming capability via articles like this one. While the AI trend is impacting businesses everywhere, you will start seeing it even in the applications you use most. And with Excel often being used as a person’s first introduction to data analysis, it is a natural fit.
Where do I get help?
There are many avenues for obtaining more information on a specific topic (Google, YouTube, etc). I typically start with a Google search on some keywords of what I am trying to tackle because there are numerous online communities that discuss how to do things in Excel. An online forum recently reached out to me and linked me to a repository of training videos about Excel. All of them are free, great resources. Here is the link to Guru99’s training on data validation, filters, and grouping. On the right-hand side of the Web page is a listing of its Excel tutorials.