PivotTables are one of Excel’s most powerful features. You can quickly extract significance from a large data set. But when you create PivotTables, some error values may appear, especially if the source data for your PivotTable contains errors. You can make these errors a little less glaring by replacing them with blank cells.
Try this: Click within your PivotTable, then click on the PivotTable Tools Analyze contextual tab. In the PivotTable group, click on Options, then choose Options from the resulting menu to open the PivotTable Options dialog box. Select the Layout And Format tab, and in the Format area, click on the For Error Values Show checkbox. Leave the corresponding text box blank. Click OK to dismiss the PivotTable Options dialog box, thereby replacing all error values with blank cells.
Another idea: When you operate a worksheet, there are some data which you don’t want others to see. You can hide entire rows and columns of data from your view without losing the data.
How: Select the row(s) or column(s) you want to hide, right-click and select Hide from the resulting dropdown list.
Unhide the data: Simply select the adjacent rows or columns of data (for example, if you’ve hidden row 3, select rows 2 and 4), right-click, and select Unhide from the resulting dropdown list.
Important: If you’ve hidden the first row of data in your worksheet, you can’t select the adjacent rows. Instead, type A1 in the Name box to select the hidden cell A1. Select the Home tab on the main ribbon, and in the Cells group, click on Format | Hide And Unhide | Unhide Rows.
You can hide multiple rows and columns, whether they are adjacent or non-adjacent. Simply press and hold [Ctrl] while selecting each row or column you’d like to hide, then right-click and select Hide. You may not hide rows and columns at once; these actions must be done separately.
Quick tip: To unhide all hidden rows or columns at once, select the entire worksheet by clicking the cell to the left of column A and above row 1. Right-clicking won’t work here; it doesn’t allow you to differentiate between unhiding your hidden rows and unhiding your hidden columns. Instead, select the Home tab on the main ribbon and in the Cells group, click on Format. In the Visibility area, select Hide And Unhide, then choose Unhide Rows or Unhide Columns.
Kick Your Data Analysis Up a Notch
For more ideas on how to use PivotTables for tracking customers, finding sales trends, creating invoices, charting progress, making important calculations and doing data analyses, check out Dartnell’s The Excel PivotTable Handbook – Your Compact Guide To PivotTables. Whether you are a beginner trying to get more out of Excel, or a seasoned user who wants to delve deeper into the mysteries of PivotTables, this book offers useful guidelines on optimizing PivotTables and more of Excel’s features.
More expert training in Excel and other Microsoft Office applications is available in AudioSolutionz’s “Microsoft Office Training Virtual Boot Camp,” where you’ll learn time-saving tips and techniques for working with PivotTables in Excel and more MS Office application functions in more efficient and effective ways.
