Excel: Organizing Sheets

There are two types of people in the Excel world: people who rename their sheets by double clicking, and people who rename their sheets by right clicking. A lot of us are double clickers, and therefore have never noticed the plethora of options for organizing sheets available on the right click menu. There are some very useful organization tools available to you on this menu… Let’s take a look.

Exercise File

You could really follow along on any old Excel spreadsheet, but if you would like one to experiment upon, here is the exercise from the Form Entry blog entry: ExcelSampleWorkBook

This workbook contains a list of fictional course grades that work with in some of the Excel trainings.

Right Click Menu

To try out the options below, hover your mouse over the sheet name (Fictional Course Grades) and right click. A menu will appear to select the options lined out below.

RIghtClickMenu

Rename

This is the one most of you know about already. You could also rename a sheet by double clicking on it.

Right Click Menu, Rename Selected

Move or Copy

Okay, this is such a useful time saver. If you have a sheet you would like to duplicate or move to another location, rather than selecting the entire contents of a sheet and copy/pasting it, consider this option.

Right click menu, Move or Copy selected

When you select Move or Copy a pop up will appear with some more options.

Move or Copy options

  • The dropdown at the top will allow you to select any open Excel Workbook to move or copy your sheet to. So if you have other Excel workbooks open right now, you will see a list of all of them here.
  • The center section allows you to choose where in the workbook you would like the sheet to go (in front of or behind existing sheets).
  • The Create a copy box is something you would want to check if you don’t simply want to move the sheet, you would also like it to remain in its original location.

Create a Copy Checked

Check this box and press OK. Notice you now have two sheets with the same content in the same workbook.

Two sheets now in Excel

This is especially handy if you are wanting to create multiple versions of tables, charts, or data lists that are similar enough in layout that you don’t want to reinvent the wheel every time.

Color Code Sheets

On to many people’s favorite organizational technique: color coding!

Right click menu tab color selected

Select the Tab Color option. This will bring up the full list of theme colors and a full color wheel under More Colors.

Color options

I selected the orange standard color. Notice how it will look a little bit different depending on if you have selected the color coded sheet. Typically the color appears a little bit faded when selected. I created a Sheet 3, and selected it in the image below… you can see how it looks different from the first two sheets.

Sheets are color coded selected sheet appears lighter

Hide Sheets

In Excel Essentials we talk about hiding rows and columns. In Advanced Formulas we talk about how sometimes you want to put formulas on one sheet that reference another. Here is a great feature that brings the two concepts together. Maybe you want to put a list of data on a hidden sheet, and only show the summary pivot table on another. Hiding a sheet allows you to do that.

Right click menu hide selected

Just remember, just like with hidden rows and columns, anyone will have the ability to unhide them. To unhide a sheet, right click on top of the existing sheet, and select Unhide.

 right click menu unhide selected

We talk about protection options in Advanced Formulas, which can address some of the privacy issues with hiding sheets and rows, but protection comes with some large caveats, so if you are interested in learning more about protection, please come to an Excel Advanced Formulas session. Plus, it is just fun to learn about formulas, so come anyway!

…or of course feel free to email or call me and we can chat about it. 😊

Thoughts?

What do you think? Will any of these organization tips help you with your Excel workbooks?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

  • Brenda Achey (as Flash Phil)

Brenda Achey