CoverAcknowledgementsExcel OverviewTime SaverAuto Fill and Fill SeriesCell ReferencingAbsolute and Relative ReferencingNavigation ShortcutsManaging CellsSorting and FilteringHiding and GroupingPaste SpecialNamed RangesRankingRoundingSum and CountManaging WorksheetsLookup Functions Data Validation IF Statements Formula Errors Text FunctionsText to ColumnsLogic Functions 2Date and TimeConditional Formatting 1Standards of Professionalism Spreadsheet Formatting and Design Basic ChartsDashboardsConditional Formatting 2Spreadsheet Protection Basic Pivot TablesAdvanced Pivot Tables and Slicers Logic Functions 3Advanced Lookup FunctionsLoan Amortization Schedule Advanced ChartsMacros 1Macros 2Macros 3How to Use Search Engines

Standards of Professionalism

Spreadsheets can be used for many purposes. Oftentimes, other people will use or review your spreadsheet, so it’s important that your work follows standards of professionalism.

In this chapter, we'll work on making a confusing spreadsheet into a professional one. In the spreadsheet (Figure 14.1), we have textual notes, data tables, and a chart. However, there are many issues with it: the text and numbers are in varying formats, the sections are uneven, and the chart axes are not clearly labeled.

Standards of Professionalism

Review this document about the Standards of Professionalism.

Practice Spreadsheet

Use this workbook for the chapter.

BUS115_image_14.1.jpg
Figure 14.1

First, the font style needs to be consistent across the spreadsheet.

  1. Select the whole spreadsheet by pressing the arrow in the top-left corner above the first row's number.
  2. Go to the Page Layout tab.
  3. Press the Fonts button in the ribbon toolbar.
  4. Select the desired font style (Arial). (See Figure 14.2)
Figure 14.2
 

Borders and fill colors may also be removed while cells are rearranged.

  1. Select the whole spreadsheet by pressing the arrow in the top-left corner above the first row's number.
  2. Press the down arrow on the Borders button on the Home tab and select No border.
  3. Press the down arrow on the Fill color button on the Home tab and select No Fill.

Data Input Table

The data input table needs to be formatted to clearly identify cells the user can modify, and it includes cells containing values that depend on the user's input. The dependent cells need formulas referencing the user's input to prevent manual data input for all cells each time. First, we will format the data values as currency and percentage respectively.

  1. Select the cells containing currency values in the From and To columns.
  2. Select the Number Format drop-down list from the Home tab and select Currency.
  3. Press the Increase Decimal or Decrease Decimal buttons if needed to adjust the decimal places to show two digits after the decimal.
  4. Select the cells containing percentage values in the % (percent) column.
  5. Select the Number Format drop-down list from the Home tab and select Percentage.
  6. Press the Increase Decimal or Decrease Decimal buttons to adjust the decimal places to show one digit after the decimal.
  7. Select all cells (including the From, To, and % headers).
  8. Press the Align Right button on the Home tab to align cells according to standards of professionalism.
BUS115_image_14.3.png
Figure 14.3
 

Format Input Data

The Annual Sales and Combined Comission cells should be formatted with a fill color to signify user input. Excel provides a convenient preset input format for this situation.

  1. Select the cells intended for input in the Annual Sales and Combined Comission columns.
  2. Select the preset Input format from the list of styles on the Home tab of the ribbon toolbar.

Next, the dependent cells need to be rewritten with formulas based on the input cells. In this case, the values under the Annual Sales's To column are one less than the From input value on the next row. We want the To column's cells to reference the input on the next row and subtract one.

  1. Select the first cell and type an equal sign ( = ) to begin a formula.
  2. Select the cell to the left on the next row.
  3. Type a hyphen ( - ) for subtraction and a 1.
  4. Press Enter to complete the formula (=B9-1). (See Figure 14.4)
  5. Select the completed cell and double click the small square in the bottom-right corner to duplicate the formula down the table's remaining cells in the column.
BUS115_image_14.4.png
Figure 14.4
 

The Combined Comission dependent cells will need a formula to calculate the input percentage of the Annual Sales values. To do it, the formula multiplies the associated Annual Sales' From or To cell by the percentage input on the same row.

  1. Select the first cell in the table under From and type an equal sign ( = ) to begin a formula.
  2. Select the related From cell in the Annual Sales table.
  3. Type an asterisk for multiplication.
  4. Select the percentage input cell.
  5. Press the F4 key three times to anchor the designated column but not the row.
  6. Press Enter to complete the formula (=B8*$D8). (See Figure 14.5)
  7. Select the completed cell and press the small square in the bottom-right corner and drag it across the other relevant From and To cells to copy the formula.
BUS115_image_14.5.png
Figure 14.5
 

Format Headings

A space can be added between the tables for better readability. The headings may also need formatting to clearly label the tables for other users. In this case, a light fill color can be added to the heading and neighboring cells equal to the table's width. The revised data input table should look like Figure 14.6.

Note: The heading cells do not need to be merged but should be aligned left and text wrapping removed for professionalism.

BUS115_image_14.6.png
Figure 14.6
 

Explanatory Text

The spreadsheet also contains some text that appears to provide information related to the data. In Figure 14.7, we can see commission and schedule are spelled wrong, numbers are interchangeably spelled out and in numeral form, and some words are abbreviated. For the standards of professionalism, be sure words are spelled correctly, punctuation is used appropriately, and style is consistent.

BUS115_image_14.7.jpg
Figure 14.7
 

The first and second items in the list appear to describe the goal of the spreadsheet, so they could be consolidated to a single line and relocated underneath the spreadsheet's title. Additionally, we may want to present the information in a text box with a bulleted list instead of individual cells.

 

Text Boxes

A text box can be placed on top of the spreadsheet cells, and it can be resized and moved around freely.

  1. Go to the Insert tab on the ribbon toolbar.
  2. Press the Shapes button.
  3. Select the Rectangle.
  4. Move the cursor to an empty space of the spreadsheet, press the left mouse button and drag the cursor until the shape's desired size is achieved.
  5. Type or paste the textual content into the box.
BUS115_image_14.8.png
Figure 14.8
 

The fill and font colors will need to be adjusted to have a white background, dark-gray/black text, and an optional dark-gray border; dark gray is an effective softer option to help it blend in. Extra spacing can be added between bulleted items for better readability. We may even add a "Notes" header to identify the content in the box. The result should look like Figure 14.9.

BUS115_image_14.9.jpg
Figure 14.9
 

Data Chart Presentation

The data chart references a separate table for its data. The table is recommended to be located on a separate spreadsheet because it is a background function not relevant to the dashboard.

The chart's data table contains sales, percentages, and commissions numbers. The sales numbers show a steady increment, but we want it to use a formula to automatically update the increment range from a single cell. We will need a new input cell denoted as Increment, and formatted as currency.

Formatting Reference Data

Next, the Sales column values need to use a formula referencing the Increment cell and adding its value.

Note: The first cell in the table must begin with a zero and not a formula.

  1. Select the second cell in the Sales column.
  2. Type an equals sign ( = ).
  3. Select the cell above (the first cell containing a value of zero).
  4. Type a plus sign ( + ).
  5. Select the Increment input cell.
  6. Press Enter to complete the formula.
  7. Select the completed cell again and double-click the small square in the bottom-right corner of the cell to duplicate the formula down the table's remaining Sales column cells.
BUS115_image_14.10.png
Figure 14.10
 

The Percent column can use the VLOOKUP function; this function will be taught in more detail in a later chapter. The VLOOKUP function will reference a specific array (or selection) of cells in the previous input data table. Then, it will identify a specific value from the previous table based on the Sales column value and copy the correlating percentage.

  1. Replace the first cell in the Percent column by typing an equals sign ( = ).
  2. Type VLOOKUP and a left parenthesis ( ( ).
  3. Select the cell in the Sales column on the left.
  4. Type a comma ( , ).
  5. Select the Annual Sales' From and To cells, and the Combined Commission's percent range of cells. (See Figure 14.11)
    BUS115_image_14.11.png
    Figure 14.11
     
  6. Type a comma ( , ).
  7. Type a four ( 4 ) to indicate the column from the array that a value will be sourced.
  8. Type a comma ( , ).
  9. Type TRUE to find the closest value.
  10. Type a right parenthesis ( ) ) and press Enter to complete the formula.
  11. Select the completed cell again and double-click the small square in the bottom-right corner to copy the cell down the table's remaining cells in the column.
BUS115_image_14.12.png
Figure 14.12
 

Lastly, the Commissions column needs a formula to multiply the Sales value by the percentage.

  1. Replace the first cell in the Commissions column by typing an equals sign ( = ).
  2. Select the Sales cell on the left in the same row.
  3. Type an asterisk ( * ).
  4. Select the Percent cell on the left in the same row.
  5. Press Enter to complete the formula.
  6. Reselect the completed cell and double-click the small square in the bottom-right corner to copy the formula down the table's remaining cells in the column.

Note: The cells in this table will need to be formatted similarly to the data input table with currency and percent formats.

Notice the chart's data table includes numbers above the previous data input table's $2,000. To make the table and the chart more effective, we may use an increment of $250 in the increment input cell. The excess data beyond $2,000 can be deleted and the chart's references updated to the shorter table. Additionally, we can reduce the number of decimal places to show none and simplify the chart.

Chart Design

Now the chart itself needs a small update. In particular, the axes need clear labels.

  1. Select the chart.
  2. Go to the Chart Design tab.
  3. Press the Add Chart Element button and select Axis Titles.
  4. Select Primary Horizontal.
  5. Repeat step 2 and step 3 to add Primary Vertical.

The horizontal (bottom) axis needs to be labeled as Sales, and the vertical (left side) axis needs to be labeled Commissions. The chart's border and title need to be removed because we'll add a heading in the spreadsheet's cells and format it similar to the data input table headings; the chart's heading will read Potential commissions by sales bracket. (See Figure 14.13)

Figure 14.13

 

Highlighting Data Gaps

The chart's purpose could be to identify gaps or leaps in increase. To present the data effectively, we might want to direct attention to the gaps and add an explanatory note. First, the data line should be replaced with marker points to show individual data placement.

  1. Select the data line.
  2. Go to the Format tab.
  3. Press the Format Selection button on the left side of the toolbar to open the Format Data Series side menu.
  4. Select the Marker button on the side menu's Fill & Line tab. (See Figure 14.14)
    BUS115_image_14.14.png
    Figure 14.14
     
  5. Select Built-in and choose a circular marker type.
  6. Format the marker for professionalism and readability as needed.
  7. Select the Line button and choose No line.

Then, brackets can be added to emphasize the gap and a text box can be added to briefly explain the increase.

  1. Go to the Insert tab.
  2. Press the Shapes button and select a brace style shape from the Basic Shapes category.
  3. Draw the brace by pressing the mouse button at the starting point and dragging the cursor to the desired size.
  4. Use the rotate button on the selected brace to rotate the shape.
  5. Move the brace near the data gap to emphasize it.
  6. Press the Shapes button and select a text box from the Basic Shapes category.
  7. Draw the box by pressing the mouse button at the starting point and dragging the cursor to the desired size.
  8. Type the data explanation into the box.
  9. Move the text box near the previously drawn brace and resize the box if needed.
  10. Go to the Shape Format tab on the toolbar.
  11. Press the Shape Fill button and select No Fill.
  12. Press the Shape Outline button and select No Outline.
BUS115_image_14.15.png
Figure 14.15
 

Professional Summary

Hide Columns

Extra columns and rows can be hidden to improve a dashboard's design.

  1. Select the first column that needs to be hidden.
  2. Press Ctrl + Shift + Right arrow keys to select all columns to the end of the spreadsheet.
  3. Right-click a selected column's label designation and select Hide.
    1. Alternatively, press the Format button on the toolbar's Home tab and select Hide & Unhide, then Hide Columns.
  4. Repeat step 1, step 2, and step 3 for rows.

It takes deliberate thought to layout a dashboard in a way that makes sense. All of the standards of professionalism practices in this chapter can be used in other worksheets to make them legible and usable. As you design your own dashboards and worksheets, you should design with the intent to make it easy to read and understand. The final result might look like Figure 14.16 below.

BUS115_image_14.16.png
Figure 14.16
 

Print Layout Setup

Finally, the new dashboard may need to be printed out for handing out to others for review.

  1. Select the relevant area of the dashboard to be printed.
  2. Go to the Page Layout tab.
  3. Press the Print Area button and select Set Print Area.
  4. Select the Width and Height scales on the toolbar to one page.
  5. Press the Orientation button and select Landscape if needed.

The dashboard will be ready to print.

BUS115_image_14.17.png
Figure 14.17
 

Supplemental Resource

End-of-Chapter Survey

: How would you rate the overall quality of this chapter?
  1. Very Low Quality
  2. Low Quality
  3. Moderate Quality
  4. High Quality
  5. Very High Quality
Comments will be automatically submitted when you navigate away from the page.
Like this? Endorse it!