Excel Dashboards & Reports For Dummies. Michael Alexander
underline...FIGURE 5-5: Use the Border tab of the Format Cells dialog box to customize your...FIGURE 5-6: Single accounting underlines effectively call out your column heade...FIGURE 5-7: Use number formatting to eliminate clutter in the table and draw at...FIGURE 5-8: Send your labels and headers to the background by subduing their co...FIGURE 5-9: Before and after applying table design principles.FIGURE 5-10: Choose a base format.FIGURE 5-11: The Type input box allows you to customize the syntax for the numb...FIGURE 5-12: Go to the Custom screen of the Format Cells dialog box.FIGURE 5-13: Formatting numbers applies only a cosmetic look. Look on the formu...FIGURE 5-14: Custom number formatting that shows zeroes as
n/a.FIGURE 5-15: Dates and times can also be formatted using the Format Cells dialo...6 Chapter 6FIGURE 6-1: Although this KPI summary is useful, it cannot show a full-year tre...FIGURE 6-2: Sparklines allow you to add trending in a compact space, enabling y...FIGURE 6-3: Three types of sparklines.FIGURE 6-4: Data that you want to summarize with sparkline graphics.FIGURE 6-5: Use the Create Sparklines dialog box to specify the data range and ...FIGURE 6-6: Column sparklines summarize the precipitation data for nine cities.FIGURE 6-7: A sparkline at various sizes.FIGURE 6-8: Sparklines in merged cells (E2:I7 and E9:I14).FIGURE 6-9: Sparklines can use data in hidden rows or columns.FIGURE 6-10: The bottom group of sparklines shows the effect of using the same ...FIGURE 6-11: Sparklines display the number of pages read per month.FIGURE 6-12: Using Win/Loss sparklines to display goal status.FIGURE 6-13: The axis in the sparklines represents the goal.FIGURE 6-14: The sparkline displays the values as though they are at equal time...FIGURE 6-15: After you specify a date axis, the sparkline shows the values accu...FIGURE 6-16: Creating a sparkline from data in a table.
7 Chapter 7FIGURE 7-1: The predefined conditional formatting scenarios available in Excel.FIGURE 7-2: The Highlight Cells Rules scenarios apply formats if specific condi...FIGURE 7-3: Each scenario has its own dialog box you can use to define the trig...FIGURE 7-4: Cells greater than 400 are formatted.FIGURE 7-5: The Top/Bottom Rules scenarios apply formats if specific thresholds...FIGURE 7-6: Each scenario has its own dialog box you can use to define the trig...FIGURE 7-7: With conditional formatting, you can easily see that September thro...FIGURE 7-8: Applying Data Bars.FIGURE 7-9: Conditional formatting with Data Bars.FIGURE 7-10: Applying Color Scales.FIGURE 7-11: Conditional formatting with Color Scales.FIGURE 7-12: Applying Icon Sets.FIGURE 7-13: Conditional formatting with Icon Sets.FIGURE 7-14: With a custom formatting rule, you can tag the above-average value...FIGURE 7-15: Select the target range and then select New Rule.FIGURE 7-16: Select the Format All Cells Based on Their Values rule and then us...FIGURE 7-17: Change the Type drop-down boxes to Formula and enter the appropria...FIGURE 7-18: Too many icons can hide the items you want to draw attention to.FIGURE 7-19: Open the Conditional Formatting Rules Manager dialog box and click...FIGURE 7-20: This new rule is meant to apply to any cell value that you don’t w...FIGURE 7-21: Click Stop If True to tell Excel to stop evaluating those cells th...FIGURE 7-22: This table is now formatted to show only one icon.FIGURE 7-23: Showing Data Bars inside the same cell as values can make it diffi...FIGURE 7-24: Edit the formatting rule to show only the Data Bars, not the data.FIGURE 7-25: Data Bars, cleanly placed next to the data values.FIGURE 7-26: Conditional Formatting Icon Sets enable trending visualizations.FIGURE 7-27: The up arrow indicates an upward trend; the down arrow indicates a...FIGURE 7-28: You can adjust the thresholds that define what up, down, and flat ...FIGURE 7-29: Use symbols to add an extra layer of analysis to charts.FIGURE 7-30: The starting data with a holding cell for symbols.FIGURE 7-31: Use the Symbol dialog box to insert symbols into the holding cell.FIGURE 7-32: Copy the newly inserted symbols to the Clipboard.FIGURE 7-33: Create a custom number format using the symbols.FIGURE 7-34: Your symbols are now part of the number formatting.FIGURE 7-35: Add the Camera tool to the Quick Access toolbar.FIGURE 7-36: Not surprisingly, the icon for the Camera tool looks like a camera...FIGURE 7-37: Enter some simple numbers in a range and create a basic chart from...FIGURE 7-38: A live picture is created via the Camera tool.FIGURE 7-39: Use the Camera tool to get multiple source ranges into a compact a...FIGURE 7-40: A peekaboo tab.FIGURE 7-41: A deconstructed view of the peekaboo tab.FIGURE 7-42: A visual banner made with shapes.FIGURE 7-43: Combine shapes with a chart to save dashboard real estate.FIGURE 7-44: Use the Edit Points feature to construct your own shape.FIGURE 7-45: Using a newly constructed shape to create custom infographic eleme...
8 Chapter 8FIGURE 8-1: Line charts are the chart of choice when you need to show trending ...FIGURE 8-2: Area charts can be used to trend over a large time span.FIGURE 8-3: Using columns and lines emphasizes the trending differences between...FIGURE 8-4: Vertical scales should always start at zero.FIGURE 8-5: Always set the Minimum value of the vertical axis to zero.FIGURE 8-6: A standard linear scale doesn’t allow for accurate trending in this...FIGURE 8-7: Using the logarithmic scale helps bring out trending in charts that...FIGURE 8-8: Setting the vertical axis to logarithmic scale.FIGURE 8-9: Choose to abbreviate category names instead of changing alignment.FIGURE 8-10: To save real estate on your dashboard, try labeling only certain d...FIGURE 8-11: A bar chart can prove to be effective when trending days extend to...FIGURE 8-12: Excel is smart enough to recognize and plot multiple layers of lab...FIGURE 8-13: You can show trends for different time periods side by side.FIGURE 8-14: The source data needed to display side-by-side trends.FIGURE 8-15: Change the chart type to Stacked Column Chart to present columns i...FIGURE 8-16: If you want to separate each year with a space, simply add a space...FIGURE 8-17: A stacked time comparison allows you to view and compare two years...FIGURE 8-18: Start with a structure containing the data for two time periods.FIGURE 8-19: You often need to trend two different units of measure, such as co...FIGURE 8-20: The trending for percentage of labor cost gets lost at the bottom ...FIGURE 8-21: Placing a data series on the secondary axis.FIGURE 8-22: Thanks to the secondary axis, both trends are clearly defined.FIGURE 8-23: Changing the chart type of one data series can underscore comparis...FIGURE 8-24: The spike in October warrants emphasis.FIGURE 8-25: Right-click a single data point to see a shortcut menu of options.FIGURE 8-26: The chart now draws attention to the spike in October and provides...FIGURE 8-27: Use a simple line to mark particular events along a trend.FIGURE 8-28: You can easily see where sales trending ends and forecast trending...FIGURE 8-29: Start with a table that places your actual data and your forecasts...FIGURE 8-30: Directional trending (top) can help you reveal trends that may be ...FIGURE 8-31: The volatile nature of this data makes it difficult to see the und...FIGURE 8-32: A four-month moving average trend line has been added to smooth th...FIGURE 8-33: Applying a 4-month moving average trend line.FIGURE 8-34: A calculated smoothing column feeds a new series to your chart.FIGURE 8-35: Plotting the smoothed data reveals the underlying trend.
9 Chapter 9FIGURE 9-1: Top and bottom displays that emphasize certain metrics.FIGURE 9-2: You can use conditional formatting to add visual components to your...FIGURE 9-3: Start with a pivot table that contains the data you want to filter.FIGURE 9-4: Select the Top 10 filter option.FIGURE 9-5: Specify the filter you want to apply.FIGURE 9-6: You can interactively filter the pivot table report to instantly sh...FIGURE 9-7: You now have two pivot tables that show top and bottom displays.FIGURE 9-8: This chart highlights the top five quarters with different font and...FIGURE 9-9: Using the LARGE function returns the nth largest number from a data...FIGURE 9-10: Build a new chart feeder that consists of formulas that plot value...FIGURE 9-11: After you add data labels to the top five data series and do a bit...FIGURE 9-12: Entering #,##0;; as the custom format for a data label hides all z...FIGURE 9-13: Use the SMALL function to highlight the bottom values in a chart.FIGURE 9-14: A histogram showing the distribution of units sold per month among...FIGURE 9-15: Select your data then choose a Histogram as the chart type.FIGURE 9-16: Excel’s initial attempt at creating a histogram.FIGURE 9-17: Use the Format Axis task pane to adjust how the chart groups data ...FIGURE 9-18: A cleaner histogram after making a few adjustments to the bins.FIGURE 9-19: Start with your raw data table and a bin table.FIGURE 9-20: Type the FREQUENCY formula you see here and then be sure to hold d...FIGURE 9-21: Build a simple chart feeder table that creates appropriate labels ...FIGURE 9-22: Plot your histogram data into a column chart.FIGURE 9-23: To eliminate the spaces between columns, set the Gap Width to 0%.FIGURE 9-24: The cumulative percent series shows the percent of the population ...FIGURE 9-25: In a new column, create a formula that calculates the percent of t...FIGURE 9-26: The initial chart will need some formatting to make it look like a...FIGURE 9-27: Place your data values in the Rows drop zone and the Sales Rep fie...FIGURE 9-28: The Grouping dialog box.FIGURE 9-29: