A Summary Sheet In Excel

Why?

A summary sheet can be used to quickly view key details from multiple sheets in a large workbook. A correctly formatted summary sheet can be used as the basis for a mail merge in Word.

How

This morning’s client help call came from a client using Excel to track employee performance reviews. Once the reviews are completed, they will use Word’s Mail Merge feature to generate individual checklists for each employee.

This process starts with a well-designed Excel workbook.

A view of the Summary Sheet
This summary sheet can be used for many purposes, including as the data file for a mail merge.

In the example above, there is a summary sheet which pulls the critical information from each individual employee’s sheet. Regardless of the scenario, a summary sheet is frequently used to present critical information at a glance.

Breaking out the individual sheets in the workbook
Each employee sheet has a similar layout.

Copying and pasting information or creating individually linked formulas is tedious. Instead; try creating a named formula.

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

The GET.WORKBOOK function is a leftover from the old Excel 4 macro language. This is why the file needs to be saved as a macro enabled file. Even though there is no VBA in it. Finding documentation on GET.WORKBOOK is hard, since Microsoft doesn’t officially support it anymore. But a kind soul has posted here https://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/getworkbook-help-in-macro-language-in-excel-2011/a359708d-b5b7-48ba-8d1e-c4114d4642d2

The GET.WORKBOOK functions cannot be entered directly in a cell, instead you must place them in a named formula. Here is what it looks like, using the Name Manager dialog box. The named formula can be called anything. Here I’ve used ListSheetsFunction as the name.

Adding a Named formula through the Name box
Using GET.WORKBOOK will mean your file will need to be saved as a Macro-Enabled workbook.

Once the named formula has been created, it can be used in conjunction with the INDEX function in the following formula:

=INDEX(ListSheetsFunction,ROW())
Demonstrating how the named formula captures the sheet name
After creating the formula in cell A2, copy it down column A for as many rows as there are sheets in the workbook.

You can see how the formula captures each sheet name as it is copied down the column. Note (this formula doesn’t always recalculate automatically, you may need to close/reopen your workbook or recopy the formula to force recalculation after editing sheet names).

Now that the list of sheet names is in the first column, you can use that to build the formulas that will give you the summary of each sheet in the workbook.

=INDIRECT(ADDRESS(2,COLUMN()-1,1,1,$A2),TRUE)
Adding the INDIRECT/ADDRESS formula to link to the key cells in the sheets
Depending on the layout of the detail sheets, you can copy one formula to the rest of the summary sheet.

In this case I copied the formula into cells B2:E4. Now as the fields are updated in each individual sheet, the summary will update. If new sheets are added, the formulas from the previous row can be copied down.


There are hundreds of formulas available in Excel; the trick is to cut through all the options and find the one that does the job you need done.

If you are interested in becoming a My TechGenie client, give me a call at 403-581-1275 or send an email to catharine@mytechgenie.ca. Practical, everyday technology is our specialty!

Home On The Range

Sometimes, when you’re teaching, its not about the complexity of the subject. Sometimes its a very simple piece of information that students get the most “mileage” out of.

When I’m teaching students MS Excel, the simplest thing that I teach them about is Named Ranges. Its the simplest thing to talk about, but the uses for ranges go on and on.

Excel Spreadsheet Example
A plain spreadsheet.

Above you see a standard Excel spreadsheet. Adding a range name or two (or ten) can help make it much easier to work with.

Adding a Range name to the Cell Address box.
Type the range name into the Cell Address box. Press Enter when done.

A range name can refer to a single cell or a group of cells, here I’ve selected the cell containing the total for the six month period (H11).

Click into the Cell address box (circled in red) and type in the desired name. There are some simple rules about naming ranges; the name can’t start with a number, can’t look like a cell reference (imagine how confusing that would be) and can’t use spaces and special characters (notice I’ve used an underscore to separate words). But after that it is up to you, to make your range name meaningful.

Adding a range name to a group of cells
Adding a range name to a group of cells.

If you are going to add a range name to a group of cells, select them and type the name into the cell address box. The most frequent mistake students make at this point, is that they forget to press the Enter key to confirm the range name.

Now, how do you use these range names?

Navigating your spreadsheet using range names.
Navigating your spreadsheet using range names.

First, you can quickly jump to your named ranges by using the drop-down menu. When you click on the drop-down menu in the cell address box, you’ll see a list of all the ranges you’ve added to your spreadsheet. Regardless of what sheet they are on. So you can use this to quickly jump to those cells that you work with again and again.

Range Names can replace cell references in formulas
Range Names can replace cell references in formulas.

Second, you can replace cell references in a formula with range names. Does =SUM(January) seem easier to read and understand than =SUM(B2:B10)? Then a formula that uses range names will make your spreadsheets easier to read.

Third, you can use range names in conjunction with all sorts of other Excel tools. As an example, try using range names with the Data Validation tool.

A Named Range provides the source for this data validation list.
A Named Range provides the source for this data validation list.

In the sample above, a range name provides the source list for a drop-down list.

Data Validation Result
Data Validation Result

Resulting in this drop-down list. The list will update as the list of animals changes on Sheet1.

This is a more elegant solution for using drop-down lists, since it means your source lists can be kept on another sheet, and not clutter up the working area. This is something that is impossible to do, without using a range name.

So faster navigation, easy to read formulas and access to more powerful features in Excel. What’s not to love about range names?


Looking to make a powerful spreadsheet or do you want me to make it for you? Drop me a line at catharine@mytechgenie.ca

PowerPoint: Use the Grey Space

The grey space around your PowerPoint slide is a useful area.

Picture of the workspace in PowerPoint
The grey space around a PowerPoint slide is used to hold instructions.

You can put it to work in a couple of ways:

  • Park items you’re not working with but still want to have on hand, like an image that you want to keep in the file for use later.
  • Put instructions for other users here.

Does your organization need easy to use, on brand presentation templates? Drop me a line at catharine@mytechgenie.ca, and let’s talk.

On with the show

Normally, people save their PowerPoint presentations in the default format. However, once you are on the final version of you presentation consider using the PowerPoint Show format. Saving your PowerPoint presentation as a show is easy. Use the Save As command and use the Save As Type list to show all the possibilities. Select PowerPoint Show and save as normal.

PowerPoint 2010 Save as Dialog. Not many changes here.
PowerPoint 2010 Save as Dialog. Not many changes here.

The screen shot above is from PowerPoint 2010, but you should see a similar list in subsequent versions. The show will be saved in a different file format, using the .ppsx or .pps file extension.

The result is a change in behaviour when the file is opened. Double-click on the file and it will launch immediately into Slide Show view. Much slicker than starting the presentation, allowing the audience to view your notes, finding the slide show icon and starting the presentation. If you have a presentation that uses timed transitions and you are worried about the presentation running away on you, remove the timing from the first slide. Use a mouse click to advance to the rest of your timed slides once you are ready to start. I think you’ll find this a smoother way of launching your presentation.

If you need to edit your presentation, start PowerPoint and use it to open the show. You can edit the file as you would normally. If you wish to convert it back to a regular presentation, use the Save As command and save it in the normal file format.


I help people create dynamite presentations. Drop me an email at catharine@mytechgenie.ca and we can do amazing things!