Want a quick introduction to PivotTables? Watch this 10 minute video. I demonstrate the basic features of PivotTables and feature Slicers, new to Excel 2010. And for more, view the earlier post: PivotTable Intro, Step by Step.
Here’s another cool feature of Excel: Speak Cells on Enter.
This can prove valuable as a means to verify accurate data entry. The only setup required is to add a button to your Quick Action Toolbar (QAT).
- RIGHT + CLICK on the QAT and select Customize Quick Access Toolbar.
- Set the Choose commands from drop-down to All commands, then scroll down and select Speak Cells on Enter.
- Click Add, then OK.
The button now appears on your QAT. Click button to toggle the feature on or off.
When active, each time you enter in a cell, the cell contents will be read back to you. Unlike other reader programs this voice is clear and rather pleasant (take note Acrobat).
Now if only you could select the voice, I’d take something along the lines of a HAL 9000, or K.I.T.T. model.
Additional voice features of Excel include:
- Speak Cells
- Speak Cells – Stop Speaking Cells
- Speak Cells by Columns
- Speak Cells by Rows
- Stop Listening to Voices in My Head*
*available only to select consumers. What, I am not one of them? I am so! You keep out of this.
Comments and questions are always welcome!
PivotTables are one of the most powerful tools in Excel’s data management arsenal. Surprisingly, it is also one of the most overlooked tools. A PivotTable enables the reviewer to statistically analyze data in various flexible formats. By pivoting bits of data into place you are able to create new views of worksheet data in seconds.
Figure 1 PivotTable report example
To create a PivotTable you must start with a database. For Excel’s purposes a database can be defined as:
A table, where the first row contains the column headings (field names), each row contains data, and is devoid of blank rows or columns.
First, review your data and, if necessary cleanup the data. This will include deleting unnecessary blank rows. It may also include inserting blank rows between titles and summaries.
Click here to open ‘PivotTable Intro.XLS ‘ and follow below instructions.
To insert a PivotTable report
- Place cursor on one of the data values of the table.
- On the Insert tab, click PivotTable. The Create PivotTable dialog appears.
- Confirm the settings and click OK. A new sheet is displayed, and the PivotTable Field List pane appears at right.
- Drag the fields in the pane into the appropriate area at bottom.
- Row: Region, Product
- Column: Market
- Value: Quantity
To update the PivotTable
After editing the original database, you must manually update the PivotTable to reflect those editions.
- Click on one of the values in the PivotTable.
- On the Analyze tab, in the Data group, click Refresh.
Tip Double + Click on a PivotTable value to Quick query the data. Excel will copy the supporting records onto a new sheet for your review. This data is not dynamic. To prevent unintentional editing it is recommended that you delete this sheet, following your review.
Slicers, new to version 2010, enable you to filter select records to display. In earlier versions of Excel, this is accomplished using Filters.
To Filter the PivotTable report using Slicers
- Click on one of the values in the PivotTable, to display the PivotTable Ribbon tools.
- On the Analyze tab, in the Filter group, click Insert Slicer. The Insert Slicers dialog appears.
- Check the field(s) you wish to create filters for and click OK. The Slicer pane(s) is added to the worksheet.
- Click on item(s) in the Slicer pane to display only those records.
- When generating a PivotTable, the source data must be in a ‘clean’ table.
- Slicers can be used to enhance the report layout.
- Double + Click a data point to quickly query the data.