Excel Dashboards & Reports For Dummies. Michael Alexander

the values in the row area is a frequency d...

      10 Chapter 10FIGURE 10-1: A typical chart showing performance against a target.FIGURE 10-2: Consider using variances to plot performance against a target.FIGURE 10-3: Measuring data when there’s no target for a measure.FIGURE 10-4: Start a new column and enter a formula.FIGURE 10-5: Thermometer-style charts offer a unique way to show performance ag...FIGURE 10-6: Bullet graphs display multiple perspectives in an incredibly compa...FIGURE 10-7: The parts of a bullet graph.FIGURE 10-8: Start with data that contains the main data points of the bullet g...FIGURE 10-9: Switch the orientation of the chart to read from columns.FIGURE 10-10: Use the Change Chart Type dialog box to change the Target series ...FIGURE 10-11: Adjust the marker to a dash.FIGURE 10-12: Be sure to delete the newly created secondary vertical axis.FIGURE 10-13: Your formatted bullet graph.FIGURE 10-14: To add more data to your chart, manually expand the chart’s data ...FIGURE 10-15: Expanding the data source automatically creates new bullet graphs...FIGURE 10-16: Try setting gap widths to zero to create clean-looking qualitativ...FIGURE 10-17: Rotate all labels so that they’re on their sides.FIGURE 10-18: A horizontal bullet graph.FIGURE 10-19: You can create a component that plots performance against a targe...FIGURE 10-20: Create a chart feeder that contains formulas that define the data...FIGURE 10-21: Add a row for the performance values.FIGURE 10-22: Use the Change Chart Type dialog box to change the Values series ...FIGURE 10-23: Format the Lower Limit series so that it’s hidden.

      11 Chapter 11FIGURE 11-1: Enabling the Developer tab.FIGURE 11-2: The Record Macro dialog box.FIGURE 11-3: Start recording a new macro called AddDataBars.FIGURE 11-4: Start recording a new macro called RemoveDataBars.FIGURE 11-5: Use the Macro dialog box to select a macro and run it.FIGURE 11-6: Your macro applies Data Bars automatically!FIGURE 11-7: The RemoveDataBars macro removes the applied Data Bars.FIGURE 11-8: You can find the Form Controls menu on the Developer tab.FIGURE 11-9: Assign a macro to the newly added button.FIGURE 11-10: The Trusted Locations menu allows you to add directories that are...FIGURE 11-11: Use macros to build buttons that help users navigate your reports...FIGURE 11-12: This report allows users to choose their perspective.FIGURE 11-13: The macros behind these buttons rearrange the data fields in a pi...FIGURE 11-14: Macros can offer your users prerecorded views.FIGURE 11-15: You can give your audience a choice in how they view data.FIGURE 11-16: Form controls and ActiveX controls.FIGURE 11-17: Nine labeled Form controls that you can add to your worksheet.FIGURE 11-18: Right-click and select Format Control to open a dialog box with t...FIGURE 11-19: Formatting the Check Box control.FIGURE 11-20: The two states of the check box.FIGURE 11-21: A check box can help create the disappearing data series effect.FIGURE 11-22: Start with raw data and a cell in which a Check Box control can o...FIGURE 11-23: Create a staging table that will feed the chart. The values of th...FIGURE 11-24: When cell A12 reads True, 2020 data is displayed; when it reads F...FIGURE 11-25: You can use check boxes to control how much data is shown in your...FIGURE 11-26: Formatting the Option Button control.FIGURE 11-27: This chart is dynamically fed different data based on the selecte...FIGURE 11-28: Start with the raw data sets and a cell where the option buttons ...FIGURE 11-29: Create a staging table and enter this formula in the first cell.FIGURE 11-30: Formatting the Combo Box control.FIGURE 11-31: Use combo boxes to give your users an intuitive drop-down selecto...FIGURE 11-32: Start with the raw data set and a cell where the combo box can ou...FIGURE 11-33: Create a staging table that uses the INDEX function to extract th...FIGURE 11-34: Create a chart using this clean staging table.FIGURE 11-35: Formatting the List Box control.FIGURE 11-36: This list box feeds the region selection to multiple charts, chan...FIGURE 11-37: Start with the raw data sets that contain one line per region.FIGURE 11-38: Add a list box and note the cell where the output value will be p...FIGURE 11-39: Use the CHOOSE function to capture the correct data corresponding...FIGURE 11-40: Create similar CHOOSE formulas for each row/category of data, and...

      12 Chapter 12FIGURE 12-1: Default pivot table Filter fields do not work together to limit fi...FIGURE 12-2: Filter fields show the text (Multiple Items) when multiple selecti...FIGURE 12-3: Slicers work together to show you relevant data items based on you...FIGURE 12-4: Slicers do a better job of displaying multiple item selections.FIGURE 12-5: Select the dimensions for which you want slicers created.FIGURE 12-6: Select the dimensions you want filtered using slicers.FIGURE 12-7: The fact that you can see the current filter state gives slicers a...FIGURE 12-8: Click the Clear Filter icon to reset the slicer.FIGURE 12-9: Adjust the slicer size and placement by dragging the circular posi...FIGURE 12-10: The Format Slicer pane offers more control over how the slicer be...FIGURE 12-11: Adjust the Number of Columns property to display the slicer data ...FIGURE 12-12: The Slicer Settings dialog box.FIGURE 12-13: Examples of how drastically different you can make your slicers l...FIGURE 12-14: The Slicer Styles gallery has an option for you to create your ow...FIGURE 12-15: The New Slicer Style dialog box.FIGURE 12-16: Slicers allow you to format each element separately.FIGURE 12-17: You can modify, duplicate, or delete any of your custom styles.FIGURE 12-18: Choose the pivot tables that will be filtered by this slicer.FIGURE 12-19: Select the date fields for which you want slicers created.FIGURE 12-20: Click a date selection to filter your pivot table or pivot chart.FIGURE 12-21: You can expand the range on the Timeline slicer to include more d...FIGURE 12-22: Quickly switch among quarters, years, months, and days.FIGURE 12-23: You can hijack pivot slicers and use them as more attractive Form...FIGURE 12-24: Create a simple table that holds the names you want for your cont...FIGURE 12-25: Create a slicer for the Metric field.FIGURE 12-26: Use the Slicer Settings dialog to uncheck the Display Header sett...FIGURE 12-27: Clicking an item in the slicer filters out the correct index numb...FIGURE 12-28: Use the filtered trigger cell to drive the formulas in the stagin...FIGURE 12-29: The final staging table fed via the slicer.FIGURE 12-30: You can use slicers to filter Table objects.

      13 Chapter 13FIGURE 13-1: The file-sharing options are hidden away in the Save As dialog box...FIGURE 13-2: Type the password needed to modify the file.FIGURE 13-3: A password is now needed to make changes to the file.FIGURE 13-4: Type the password needed to open the file.FIGURE 13-5: Though this sheet is protected, users can enter their data into th...FIGURE 13-6: To ensure that a cell remains unlocked when the worksheet is prote...FIGURE 13-7: Click Protect Sheet on the Review tab.FIGURE 13-8: Specify a password that removes worksheet protection.FIGURE 13-9: The Unprotect Sheet dialog box removes worksheet protection.FIGURE 13-10: The Protect Structure and Windows dialog box.FIGURE 13-11: Select Paste Special from the Home tab in PowerPoint.FIGURE 13-12: Be sure to select Paste Link and set the link as an Excel Chart O...FIGURE 13-13: Your Excel chart is now linked into your new PowerPoint presentat...FIGURE 13-14: With a linked chart, you can make changes to the raw data without...FIGURE 13-15: You can manually update links.FIGURE 13-16: PowerPoint, by default, asks whether you want to update all links...FIGURE 13-17: Open the dialog box to manage your links.FIGURE 13-18: Deselect the Automatic Update check box to turn off automatic lin...FIGURE 13-19: You can export your Excel workbook to PDF.FIGURE 13-20: Select a location for your PDF; then click the Options button.FIGURE 13-21: Excel allows you to define what gets sent to PDF.FIGURE 13-22: Click the More Options hyperlink.FIGURE 13-23: You have full control over which sheets and objects are available...FIGURE 13-24: Sharing options in an Excel web document.

      14 Chapter 14FIGURE 14-1: Fancy formatting can be overwhelming, subduing the very data you’r...FIGURE 14-2: Charts should present data as simply as possible.FIGURE 14-3: Charts with too many chart elements can become convoluted and hard...FIGURE 14-4: Formatting large numbers to millions or thousands makes for a clea...FIGURE 14-5: Select Custom in the Category list, and enter a number format code...FIGURE 14-6: Data tables enable you to show data values without overloading you...FIGURE 14-7: Adding a data table to a chart.FIGURE 14-8: The Format Data Table task pane.FIGURE 14-9: Use chart titles to present extra layers of data without taking up...FIGURE 14-10: Using sorted data in a chart improves readability and clarity.FIGURE 14-11: Pie charts can’t clearly represent more than two or three data ca...FIGURE 14-12: Bar charts are an alternative to pie charts when you have more th...FIGURE 14-13: Sometimes you work with so much data that your charts no longer m...FIGURE 14-14: Creating separate, individual charts is often better than one con...FIGURE 14-15: A skewed aspect ratio can distort your charts.

      15 Chapter 15FIGURE 15-1: Don’t use charts for charting’s sake.

      Guide

      1