Sunday, 2 November 2014

Excel Pivot & Shortcut Keys

Question:

I understand Microsoft Excel is a good start for analytics on data. Please direct me as to how to create a basic Pivot Table. You may also share me any tutorial references to learn more on Pivot feature in Excel. Also share Short Cut keys in Excel

By: Vinay Kumar

Response:

Learning to create a Pivot is fairly easy but the tough part is to know how you want the data to be presented in the Pivot. For this you need to know a good understanding of the data on hand and what you want out of the data. For this question, I assume that you are already clear about your needs.

Here is how you can quickly get a Pivot of your Excel Data:

  • Select the data range that you want to apply Pivot on and then click on the Pivot button, which is in the Toll bar ribbon under the Insert Menu. You may also use the short cut key Alt + NV
  • This will open up the Pivot dialog window where you will have option to adjust or change your your data range (as you may observe, you can also use an external data source by choosing the appropriate option) and other options.
  • Clicking ok on this dialog window will open up the Pivot table in a new work sheet (default option) and will show the Pivot Table Fields task pane on the right side. You can drag and drop the fields into Filters, Columns, Rows and Values. 
    • FILTERS: This area contains the fields that enable you to page through the data summaries shown in the pivot table by filtering out sets of data — they act as the filters. For example, if you designate the Year field from a data list as a report filter, you can display data summaries in the pivot table for individual years or for all years represented in the data list.
    • COLUMNS: This area contains the fields that determine the arrangement of data shown in the columns of the pivot table.
    • ROWS: This area contains the fields that determine the arrangement of data shown in the rows of the pivot table.
    • VALUES: This area contains the fields that determine which data are presented in the cells of the pivot table — they are the values that are summarized in its last column (totaled by default).
  • Set these in the way you want and your Pivot table is ready.

To know more about other complex and advanced features of the Pivot Table, check here.

Here is a nice Cheat Sheet containing all the Short Cut keys for Excel. If you are a key board expert, then you will want to check out this as well.