As you are learning to use Excel to develop your Executive Dashboard Templates, you will quickly learn that Defining Names are your friend.
Defined names are great in saving time, and changing ranges in formulas and as we saw in “How-to Make a Dynamic Chart Using Offset Formula” using defined names are needed to create some dynamic charts.
In Excel 2007, it is incredibly easy to add a defined name to a formula by simply typing the name as you are creating the formula. As you start to type in the name, if it matches a name then Excel will give you it as a choice, kind of like an Excel Function. It might look something like this:
That was really easy to enter in a name into Excel, however, when you start to type a name into a Chart Dialog Box, Excel doesn’t want to help you at all. You have to remember the entire name in order to refer to it in any chart dialog box.
In the example below, I am trying to add the same defined name as a series name in this chart, and Excel is not Autofilling choices for me like it did in the worksheet cell.
This isn’t too much of a problem until you have a really long name or a confusing name format. This is when you would wish you Excel could help.
Use F3 to Add A Defined Name Reference in an Excel Chart
So what can we do? Well we can use the F3 Key to bring up the Paste Name dialog box.
This will bring up the list of Names that you have defined in the workbook and you can choose the name that you want to add and it will be referenced in the chart.
In the example above, we wanted to add a dynamic series name to this chart by referencing a name that we have already defined. So first I right click on the chart, and click on “Select Data…” from the menu. This will bring up the Select Data Source dialog box and I would then select the series that I want to change in the Legend Entries (Series) area on the left and choose the Edit button. This will bring you to the Excel Edit Series dialog box. Now I want to add a dynamic series name for this series that references the Defined Name I have already created.
So I all I have to do now is press the F3 key. This will bring up the Paste Name dialog box where I can choose the defined name as my Series Name without having to type or remember the name. It would look like this:
This is an easy way to insert a name in a chart dialog box without having to remember the entire name. You can use the F3 Key in the worksheet as well, but it is not needed as much with the new features in Excel. Hopefully this will save you some time and aggravation when working with your Company Dashboard in Excel.