This chapter covers referencing and managing worksheets in Excel.
Use this workbook for the chapter.
There is a difference between workbooks and worksheets in Excel. When you open up a spreadsheet, save it, and give it a name, you have created a workbook. A workbook can contain one or more worksheets, which can be found at the bottom of the page (See Figure 5.1). The smallest reference point in a workbook is an individual cell. A page full of cells is called a worksheet. A file full of worksheets is called a workbook.
To add more worksheets, select the plus sign in the bottom left of the screen (see Figure 5.2). A new worksheet will open, and a new tab will appear at the bottom. You can use this step to make as many worksheets within the workbook as you need.
Cells in each worksheet can be referenced across worksheets. To do this, use the following steps:
Selecting the cell and dragging down or to the right will bring over more information from the other worksheet (see Figure 5.5 and Figure 5.6), just like you learned in the Absolute and Relative Referencing chapter.
If you don’t want the rest of the data from the other worksheet to appear when you drag down or to the right, you need to anchor the desired data by making it an absolute reference.
To make an absolute reference, use the following steps:
Use the following steps to rename a worksheet:
Any references to cells on the renamed worksheet will be updated with the new name. If there is a space in the name, the reference to the cell will put the worksheet name in single quotes when it appears in the formula bar. For example, if the name of the worksheet is Data Sheet, the formula bar would read, ‘Data Sheet’!$B$4 when you reference cell B4 on the Data Sheet worksheet. (See Figure 5.8)
To delete a worksheet, use the following steps:
You can click and drag the worksheet tabs to move them into the orientation that you prefer.
To make a copy of a worksheet, use the following steps:
Here is another way to move or copy a worksheet:
You can also move worksheets to different workbooks. To do this, use the following steps:
Data on copied sheets will not have any references attached to them. It will be as if it is the original worksheet, but the title of the copy will have (2) after the title to distinguish it from the original.
If you need to delete more than one worksheet at a time, use the following steps:
To further customize the tabs for your worksheets, you have the option to change the colors of the tabs. To do this, use the following steps:
You can also select More Colors, and it will give you a palette. If you select Custom, it allows you to choose a color from a wider variety of options.
Customization like this allows you to denote different things. For example, you could have one color that denotes different datasheets, and another color that denotes a dashboard or summary sheet.
Often, you will have worksheets that exist not to show user information, but to run calculations in the background or store large amounts of data. Sometimes, it makes sense to hide these worksheets so they are not seen by the end-user, or so that they don’t clutter the workbook while you’re trying to work through a different subset of data.
To hide a worksheet, use the following steps:
Although it seems like hidden worksheets no longer exist, they are still there. To unhide a hidden worksheet, use the following steps:
To hide more than one worksheet at the same time, use the following steps:
This is a nice way to help clean up the presentation for an end-user.
You can reference cells not only across different worksheets within the same workbook but also across different worksheets in a different workbook.
To reference cells from a different workbook, use the following steps:
By default, Excel makes any reference in a different workbook an absolute reference by putting dollar signs in the reference to the cell. You can remove those dollar signs using F4, or by backspacing manually.
The challenge of referencing cells in different workbooks is if you were to share or submit a file that references a different file on your computer, the user will not be able to see what was saved on your computer. They will only be able to see what is in the workbook that was sent to them. When you do these references, please be careful about what it is that you’re sharing or sending along. If you were to send just one workbook, the link would be broken. When the new person opens that one workbook, it would produce an error for them. It will still try to remember the result there, but you need to be careful when you have cells referring to data in other workbooks; those links will only be active if both workbooks are stored on the user’s computer.
These have been some helpful tips on how to manage workbooks and worksheets. As you work with different files, it is highly recommended that you spend time labeling each sheet appropriately. It makes it very easy to follow what you or someone else has already done in that file.
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/managing_worksheets.