Create a Table of Contents in Excel

In an Excel file with lots of worksheets, how do you help users navigate through the workbook?

Here are a few of the methods I’ve used. and I’d be interested in hearing about them. I’m sure you’ve found your own creative ways to deal with the Excel workbook navigation problem.

Continue reading “Create a Table of Contents in Excel”

Business Connections in LinkedIn

A couple of years ago, a client sent me an invitation to LinkedIn, a business connection website. To be polite, I joined, but never visited the site again, and didn’t make any further connections.

A few months ago, I looked at LinkedIn again. I can’t remember why — maybe someone else invited me to connect, or I saw it mentioned in a blog post somewhere. I searched a few names, found people who I know, and sent them invitations to connect.

I still didn’t see much value in the website, but visited about once a week, to see what people were up to, and to try to find a few more old contacts.

New Features Added

LinkedIn also has groups that you can join. Some are invitation only, and others are public. The groups were an interesting way to find other people with similar interests, such as Excel Users, but not much use beyond that.

Recently though, discussions were added to the groups, so people can post a comment or question, and other members of the group can respond. Finally, there’s some value in belonging to the groups, if members are active in the discussions.

I’m in an alumni group, and a couple of Excel groups, and the discussions are lively and interesting. I visit a couple of times a week, and have made a few new contacts through the group discussions.

What will I do with all these contacts? Who knows? Maybe they’ll lead to consulting opportunities, or resources when I’m stuck on a project.

Find Me at LinkedIn

You can find my profile at https://www.linkedin.com/in/debradalgleish and let me know if you’re in there too.

If you’re in LinkedIn, perhaps you’ll let me know what value you find there, if any.

_______________________

Excel Backups While You Work

How do you save your file while working in Excel?

  • Do you click the Save button, and save over the previous version?
  • Do you use Excel’s AutoSave feature?
  • Or the AutoSafe utility by Jan Karel Pieterse?
  • Do you choose Save As, and save the file with a different name?
  • Something else?

Macro to SaveAsCopy

I like to have different versions of a file, so I can go back to a previous version if something goes horribly wrong.

So, I created a macro to save my files, and added a button to the Quick Access Toolbar (QAT).

backup button on Quick Access Toolbar (QAT)
backup button on Quick Access Toolbar (QAT)

What the Macro Does

The macro saves a copy of the active file in a specified folder, adding the year, month, day, hour and minute to the file name.

For example, if the file I’m working on is named Budget2009.xls, the backup file would be named Budget2009_20081215_1008.xls if I saved it at 10:08 AM today.

NOTE: This macro does not make any changes to the active workbook, so it does NOT wipe out the Undo stack in my version of Excel. Test this on your own computer though, to make sure it’s the same for you!

The Backup Macro Code

Copy the macro below, and store it on a regular code module, in a workbook that is always open, such as the Personal Workbook.

In the code, you can change the Save directory to one that you prefer on your computer or network.

NOTE: I use C:\Backups\, but you could change that to another directory that you use.

Sub SaveBUCopy()
Dim strFile As String
Dim strName As String
Dim lExt As Long
Dim strDir As String
Dim strExt As String

strName = ActiveWorkbook.Name
strDir = "C:\Backups\"

If UCase(Right(strName, 4)) = ".XLS" Then
  lExt = 4
Else
  lExt = 5
End If

strFile = Left(strName, Len(strName) - lExt)
strExt = Right(strName, lExt)

ActiveWorkbook.SaveCopyAs strDir & strFile _
  & Format(Now, "_yyyymmdd_HhMm") & strExt
End Sub

Add Button to QAT

After you’ve added the SaveBUCopy macro to your workbook, and changed the directory name, make the macro easy to run.

To do that, follow the steps in this video, to add your macro to the Quick Access Toolbar.

There are written steps on my Contextures website — Add a Macro to the QAT.

______________________________

Excel Treatment Calendar Template

No, you don’t necessarily need treatment just because you like using Excel. 😉 Today’s post shows an Excel workbook that you can download, to plan a sequence of treatments, prescribed by your doctor.

Excel Treatment Calendar Workbook

In response to a newsgroup posting, Roger Govier created a workbook to help patients plan a sequence of treatments.

For example, people taking Warfarin, might be prescribed to take doses of 2 mg, 2mg, 3mg, 2mg, 5mg and then go back to the start of the sequence.

Sequence Setup Sheet

In the sample workbook, they would enter that sequence on the Setup sheet.

Treatment List on worksheet
Treatment List on worksheet

Treatment Sites List

Muscular Dystrophy sufferers need to inject at different sites on the body each day.

  • They could list the sites on the setup sheet, in the Treatment_List column.
  • Then, after the sequence has been entered, click the “Fill Treatments Column” button.

That button runs an Excel macro, which has the sequence copied down, to fill the Treatments Column.

Macro button Fill Treatments Column
Macro button Fill Treatments Column

View Treatments on a Calendar

After the treatment list is created, switch to the Calendar sheet, to set up the current month.

  • First, from the drop down lists, select a year and month
  • Next, pick the first treatment for the month.

The calendar will fill in with dates and treatment sequence.

Select Month for Treatment Calendar
Select Month for Treatment Calendar

Download the Sample File

You can download Roger’s sample file from the Excel Sample Spreadsheets page on my Contextures site.

In the Functions section, look for FN0016 – Treatment Calendar
________________________________

Change Blank Labels in a Pivot Table

In a pivot table, you might have a few row labels or column labels that contain the text “(blank)”. This happens if data is missing in the source data. For example, in the source data, there might be a few sales orders that don’t have a Store number entered.

Continue reading “Change Blank Labels in a Pivot Table”

Why Excel Ignores Spelling Errors-Fix

Why is Excel ignoring your spelling mistakes, like the one shown in the screen shot below? That’s not how you spell Capital!

However, the Excel message cheerfully says, “The spelling check is complete for the entire sheet.”

Spelling mistake in all caps is ignored
Spelling mistake in all caps is ignored

Upper Case Entries

If you use upper case for headings in Excel, or anywhere else on your worksheet, any spelling errors in them might go uncorrected.

That’s what happened with “CAPITEL “, in the example shown above.

To make sure that any UPPER CASE text entries are included in a spelling check, you can change an option in Excel, as shown below.

Change the Spelling option in Excel 2007

  1. To get started, click the Office button, at the top left of the Excel window.
  2. Next, click the Excel Options button
  3. In the Excel Options window, at the left, click the Proofing category
  4. at the right, scroll down to the section, When correcting spelling in Microsoft Office programs…
  5. In that section, remove the check mark from ‘Ignore words in UPPERCASE’
  6. Finally, click the OK button, to close the Options window

IgnoreCaps

Change the Spelling option in Excel 2003

If you’re using Excel 2003, follow these steps to change the spelling setting:

  1. First, click the Tools menu, and then click Options
  2. Next, click the Spelling tab
  3. Remove the check mark from ‘Ignore words in UPPERCASE’ and then click OK

__________________

Macro Creates Excel Workbooks For Entire Year

Roger Govier has created an Excel file with a macro that will set up a year’s worth of workbooks for you, at the click of a button.

It might not be the ideal workbook setup, but some people need to set these up, and this macro will certainly make the task easier.

Macro Creates Monthly Workbooks

This macro will create a series of 12 workbooks in the same folder as this workbook is stored.

You’ll be prompted to enter the year number at the beginning of the macro.

Each new workbook will be named with month and year e.g. Jan 2009.xls through Dec 2009.xls

Daily Sheets Each Month

Within each workbook, there will be a sheet for each day of the month.

There’s an option to display the numbers as ordinals, so if you click Yes for that, the sheet names would be Jan 1st, Jan 2nd and so on.

Monthly Workbook with Daily Sheets
Monthly Workbook with Daily Sheets

Get the Sample File

To download the Excel file, and to see the written steps, you can go to the Create Workbooks and Worksheets page on my Contextures site.

The zipped file contains a macro, so be sure to unblock them in Windows Explorer, before you open them.

After you open the file, enable macros, when the security message appears.

_________________________

Free Excel Conference Microsoft London April 2009

The UK Excel User Group is holding a free conference at Microsoft London in April 2009. It’s a bit too far for me, but if you can make it, I highly recommend that you register.

You’ll learn new things, meet some terrific people, and spend a couple of days discussing Excel. What could possibly be better than that?

The agenda includes sessions on charts, pivot tables, functions, names and many other topics. Even if you’re familiar with some of the topics, you’ll benefit from attending.

Excel Expert Presenters

The presenters are a very smart and creative bunch, and they’ll almost certainly show you a few tips and techniques that you haven’t tried before.

The Q&A sessions will be an excellent opportunity to discuss any Excel problems that you’ve encountered, and get solutions or suggestions from the presenters and other attendees.

When and Where

  • Date: April 1-2, 2009
  • Location: Microsoft London (Cardinal Place)
    • 100 Victoria Street
    • London SW1E 5JL
    • Tel: 0870 60 10 100

The agenda for the two days is outlined at the Excel User Group site and there’s also a Word document with conference details that you can download.

To book for either or both days, send an email to [email protected]

Tell them that Debra sent you, and you’ll get a 25% discount on the free admission! You can use Excel to figure out how much that is!  😉
_____________________________