A few days ago, I was reminded of how hard some of us work, unnecessarily. A client sent a workbook for auditing — it wasn’t performing well. I found a macro that displayed the workbook’s name in a cell. Now, the macro wasn’t slowing anything down and there’s nothing wrong with taking that route. However, unless there’s a good reason for running a macro (someone has to remember to do so) there are two easier ways to display a workbook’s name:
  • Display the filename in a custom header.
  • Display the filename in a cell using the Cell() function.

Let’s tackle the easiest solution first. To display the workbook’s name in a cell, enter the function =Cell(“filename”). This function will display the workbook’s full name. If the workbook hasn’t been saved, the function displays an empty string (“”).

Displaying the file’s name in a header or footer is a bit more traditional. To do so, complete the following steps:

  1. Choose Header and Footer from the View menu.
  2. Click Custom Header or Custom Footer.
  3. The resulting dialog box will offer three positions — Left, Center, and Right. Click inside the appropriate section.
  4. Click the File Path or the FileName icon.
  1. Click OK twice.

Excel won’t display the header in normal view. To see the header, you must click Print Preview or print content.

There are a few more differences between these two name-displaying methods:

  • Cell() includes brackets, which you probably won’t want if your only purpose is to just display the name.
  • Cell() always displays the full pathname, including the current sheet. The custom header/footer option never displays the sheet name.
  • Cell() displays the name in a sheet, which you can see while working in the file. You’ll see the header/footer only in Print Preview or on the printed page.
  • Cell() displays the workbook’s name only once, in the cell where you inserted it. The header/footer option displays the name on each printed page.
  • Cell() doesn’t automatically update if you save the workbook’s name. You must press [F9]. The header/footer updates automatically.

The method you choose for displaying the workbook’s name will depend on how you intend to use it. If displaying the workbook name is your main purpose, the Cell() function seems limited. In addition, there’s nothing wrong with using a macro to display a workbook’s name, but there are easier ways to get the job done.