If you’re creating an Excel workbook that you plan to share, the last thing you want to do is confuse those who receive it. In this guide, I’ll run through various ways to make sure others know what the spreadsheet contains, where to look for certain information, and what they need to do.
8
Add a Homepage Worksheet
One of the most effective ways to help collaborators navigate your Excel workbook is to add a homepage worksheet. This dedicated sheet—the first that people will see when they open your workbook—should contain a welcoming title, a summary of what can be found in the workbook, links to some of the key worksheets, and the steps the person reading needs to take.
In the example above, the person who opened the workbook can immediately see what it contains thanks to the title and brief description, and I’ve added clear links to the other worksheets in the colorful banner along the top—much like a website menu—so that the reader can jump straight to the right place.
Related
How to Link to Another Sheet in Microsoft Excel
Jump to another spreadsheet with one click.
Then, the instructions beneath serve as important reminders and offer further information about who can edit which parts of the workbook. What’s more, the color-coded icons (Insert > Icons)—which are also hyperlinked to other worksheets and programs—add further clarity to the details.
Consider also adding a Home icon to the other worksheets in your workbook that collaborators can click to return to the homepage.
7
Name the Worksheets, Tables, and Ranges
There are many reasons why naming worksheets, tables, and ranges in Microsoft Excel is considered good practice, not least because doing so makes your workbook more navigable.
First, renaming worksheets instantly paints a picture of what each worksheet contains. If you don’t do this, the sheets are named Sheet1, Sheet2, Sheet3, and so on by default, and this is no use to anyone trying to work their way around your workbook. More importantly, appropriately named worksheets are much more accessible to those using screen readers.
Related
Why You Should Always Rename Worksheets in Excel
Aid navigation and accessibility.
Here, as well as naming each worksheet by double-clicking each tab, I’ve color-coded them by right-clicking the tabs, hovering over “Cell Colors,” and choosing a color.
Second, if any of the data in your workbook is formatted as an Excel table, select a cell within the table, and in the Table Name field of the Table Design tab on the ribbon, give your table an appropriate name.
Taking this step means that you and others can quickly navigate to the relevant table from anywhere in the workbook through the name box in the top-left corner of the Excel window.
What’s more, formulas referencing columns in suitably named tables are easier to read and parse.
The same can be said for named ranges—they can be selected in the name box and make formulas that reference them clearer.
Related
I Always Name Ranges in Excel, and You Should Too
Tidy up your Excel workbook.
To name a range, select the relevant cells, and type their collective name in the name box.
6
Use Cell Notes for Information and Instructions
The notes tool in Microsoft Excel might seem a little old-school, but that doesn’t take away from its usefulness in making your spreadsheet easier to navigate.
Related
Comments vs. Notes in Microsoft Excel: What’s the Difference?
Don’t get overwhelmed by annotations in Excel.
Notes in Excel are simple annotations linked to individual cells that provide additional context. By default, a cell containing a note has a red tag in the top-right corner.
Add a tip on your homepage worksheet that tells people to hover over any cells containing a red tag to see more information.
When you or someone else hovers over the cell, the note appears.
To add a note to a cell, right-click the relevant cell, and select “New Note.”
Then, type the information or instructions, and click any cell in the worksheet when you’re done to exit the note edit mode.
You can’t add a note to a cell that already contains one.
5
Keep Backend Calculations and Lists Hidden
One thing likely to cause confusion for someone working their way around your workbook—especially if they’re new to Excel—is a worksheet intended for your eyes only.
The worksheet in the screenshot below—named Backend—contains lists of items used for data validation (more on this soon), as well as fixed values that are used in various formulas throughout the workbook.
To keep your workbook tidy and ensure nobody accidentally activates and edits this important backend information, you can hide the worksheet from view. To do this, right-click the worksheet tab, and click “Hide.”
To unhide a hidden worksheet, click any worksheet tab, and select “Unhide.”
If you’re concerned that people will accidentally unhide the worksheet, you can go one step further and make it “very” hidden through the Visual Basic For Applications window.
Related
How to Completely Hide an Excel Worksheet
Hide sheets in a way that is less likely to be reversed by others.
4
Make Instructions Appear and Hide Using the IFS Function
Having too many instructions on an Excel worksheet could distract the collaborator, thus hurting the collaborator’s experience instead of improving it. To overcome this issue, use the IFS function to make instructions appear and hide at the right times.
Related
How to Use the IFS Function in Microsoft Excel
Save time and eliminate the risk of errors using IFS instead of nested formulas.
Here, the instruction in column E only appears when (1) the cell in column D on that row does not contain a number, and (2) the cell in column A on that row contains text. In other words, the instruction appears as soon as someone enters their name into the next vacant row, and it disappears as soon as someone enters a numerical value in the Expenses column.
The formula I typed into cell E4 and copied down several hundred rows of column E is as follows:
=IFERROR(IFS(ISNUMBER(Employee_Expenses(@Expenses)),"",ISTEXT(Employee_Expenses(@Employee)),"Remember to enter your expenses to the nearest dollar."),"")
where
- The IFS function first tests whether the value in the Expenses column of the Employee_Expenses table contains a number, and if it does, the active cell should be blank,
- It then tests whether there’s text in the Employee column of the same table, and if there is, the active cell should contain the note inside the quotation marks, and
- The IFERROR function turns the cell blank if the IFS function returns an error.
As a result, the person inputting the data should (hopefully!) never forget to only enter whole numbers for their expenses.
3
Lock Certain Cells to Prevent Unauthorized Editing
Microsoft Excel’s ability to lock certain cells and protect the worksheet helps you ensure people know exactly where they should type and where they shouldn’t.
Related
How to Lock Cells in Microsoft Excel to Prevent Editing
Keep your most precious cells out of harm’s way.
Let’s say, in this example, you only want people to be able to click and edit cells C3, C5, and C7.
To do this, you first need to lock all the cells in the worksheet. Press Ctrl+A repeatedly until all the cells are highlighted, then press Ctrl+1 to launch the Format Cells dialog box. Then, in the Protection tab, check “Locked,” and click “OK.”
Now, you need to unlock the cells you want people to enter and edit. To do this, select the first cell you want to leave open (cell C3), hold Ctrl, and select the others (cells C5 and C7). Then, press Ctrl+1 to relaunch the Format Cells dialog box, and this time, uncheck “Locked,” and click “OK.”
Finally, in the Review tab on the ribbon, click “Protect Sheet,” ensure “Select Unlocked Cells” and “Protect Worksheet And Contents Of Locked Cells” are checked, and enter a password.
After you click “OK” and confirm the password, only the cells you unlocked will be clickable and editable.
To make the whole worksheet accessible again, click “Unprotect Sheet” in the Review tab, and enter the password you set earlier.
2
Use Conditional Formatting to Color Data Entry Cells
One way to guide collaborators’ eyes to the cells you want them to edit is to use conditional formatting.
Related
I Use Conditional Formatting in Most Spreadsheets: Here’s Why
Conditional formatting is a non-negotiable.
Here, all the entry cells are colored light blue.
However, when someone populates these cells and presses Enter, they turn white.
The steps to creating this automatic formatting are straightforward.
First, hold Ctrl as you click the cells you want to format to select them all at the same time. Then, in the Home tab on the ribbon, click Conditional Formatting > New Rule.
Next, in the New Formatting Rule dialog box, select “Format Only Cells That Contain,” and under the Format Only Cells With option, select “Blanks.”
Now, click “Format,” and choose a light-blue color in the Fill tab of the Format Cells dialog box.
Finally, click “OK” twice to close both dialog boxes, and the selected cells will turn light blue. When you type into those cells and press Enter, they’ll turn white, as they no longer meet the “blank” condition you just set.
1
Add Data Validation Rules to Limit Cell Entries
Microsoft Excel’s data validation tool helps ensure people input the right types of information into certain cells.
Let’s start with the Name column, where you want people to be able to select their name from a drop-down list.
First, in the Backend worksheet, type the list of names in separate cells in a column, and format this list as an Excel table. Also, click the filter button, and sort the names alphabetically.
The reason you need to format this list as a table is that you can easily add names to (and remove names from) the list, and the data validation rules you’re about to set up will adapt to these changes. If you use an unformatted range, you’ll run into problems if the list grows or shrinks.
Related
How to Create a Drop-Down List from a Column of Data in Excel
Which method you use depends on how your data is formatted.
Next, head back to the worksheet where you want the drop-down list to go. There, hover over the relevant header of the column in the formatted table until you see a black down arrow, and click once to select the whole column (this works whether there’s one or several rows beneath the header row).
Then, in the Data tab on the ribbon, click “Data Validation.”
In the Allow field of the Data Validation dialog box, select “List.”
Then, select the “Source” field so the cursor is flashing, head to the Backend worksheet, and select the list of names. As you do this, the sheet name and cell references will be added to the field.
Before you close the dialog box, check out the Input Message and Error Alert tabs in the dialog box. The former lets you generate a pop-up that appears when someone selects an affected cell, and the latter allows you to create an alert that people will see if they input an invalid entry into the cell.
When you click “OK,” you’ll be taken back to the worksheet where you’re adding the data validation rules, and you’ll see drop-down arrows in the affected cells, which, when clicked, display the list of names.
What’s more, any new rows you add to the table automatically adopt the data validation rules from the rows above.
While the data validation tool is most commonly used to add a drop-down list of options to a cell, this is not the only thing it can do. For example, you can create a rule that limits the number of characters in a cell or define the type of data that can be entered.
Here, I’ve restricted the input in the Hours column to a decimalized number between 0 and 160.
As well as following the tips outlined in this article, use Excel’s formatting tools—such as adjusting column widths, aligning text, and using cell borders—to make your spreadsheets clear and readable. Equally, ditching bad formatting habits—like merging cells, color-filling whole columns, and using different fonts—will improve the workbook’s performance and, thus, your collaborators’ experiences.