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.
Review this document about the Standards of Professionalism.
Use this workbook for the chapter.
First, the font style needs to be consistent across the spreadsheet.
Borders and fill colors may also be removed while cells are rearranged.
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.
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.
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.
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.
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.
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.
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.
A text box can be placed on top of the spreadsheet cells, and it can be resized and moved around freely.
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.
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.
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.
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.
Lastly, the Commissions column needs a formula to multiply the Sales value by the percentage.
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.
Now the chart itself needs a small update. In particular, the axes need clear labels.
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)
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.
Then, brackets can be added to emphasize the gap and a text box can be added to briefly explain the increase.
Extra columns and rows can be hidden to improve a dashboard's design.
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.
Finally, the new dashboard may need to be printed out for handing out to others for review.
The dashboard will be ready to print.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/standards_of_profess.