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.
Here’s a quick Office tip that applies to Word, Excel and PowerPoint.
The Mark as Final feature enables you to protect a document to discourage editing. This simple seal of protection can easily be removed by the reader, should it be determined editing is necessary.
Note, this option is not designed to prevent edits, only to ward against unintentional editing. To render the document un-editable use other alternatives (for example, saving the file password protected or distributing a PDF version of the file).
To Apply Mark as Final
- On the File tab, scroll down to Info, click Protect and select Mark as Final. A dialog will appear indicating “the file will be marked as final and saved.”
- Click OK to confirm.
When backstage view is active, a notice appears in the status bar, indicating, “An author has marked this … as final to discourage editing.” The Application title bar also indicates that the file is Read-only. Reading, printing, and viewing options continue to function, but all editing features are disabled.
To remove the Mark as Final setting and restore edit functions repeat step 1, above. Alternatively, you can click the Edit Anyway button displayed on the info bar in the backstage view .
Comments and questions are always welcome!
Here’s a quick tip that highlights PowerPoint’s easy to use Photo Album.
Remember the time when slideshow, meant a carousel of slides with you sitting in a dark room while [insert familial relation here] clicked through a series of pictures from some vacation?
No? Hmm, I may be dating myself. 😦
Take a retro moment; throw-away that text based presentation you have been struggling with (let’s face it, no one reads that stuff anyway) in favor of an old fashioned picture slideshow.
Create a Photo Album Slideshow:
- On the Insert tab, in the Images group, click the top split of the Photo Album button. The Photo Album dialog appears.
- Click the File/Disk button. The Insert New Pictures dialog appears.
- Navigate to the folder that contains the pictures to be included and select those images.
Note use CTRL + CLICK to ‘cherry pick’ images, or CLICK on the first picture and SHIFT + CLICK on the last to select that set of pictures.
- Click OK to return the Photo Album dialog.
- Optionally, adjust a picture’s settings by selecting that picture and then clicking the appropriate Move, Contrast or Rotate option.
- Select a Picture layout (e.g., Fit to slide, 2 Pictures, etc.) and select a Theme.
- Click Create.
Voila! Press F5 (shortcut) to run the slideshow
Should you need to edit the Photo album, click the bottom split of the Photo Album button and select Edit Photo Album.
Comments and questions are always welcome!
If you ever find yourself trying to perform a count on a select subset of items in a larger range, then you have reason to use the COUNTIF function. Continue reading “Excel: CountIF Function”