A viewer wrote to ask for my help in creating dynamic chart titles in Excel. He has created a series of pivot table reports and pivot charts. He wants the titles for his charts to update dynamically. That is, as he selects a new filter for his pivot table, he wants the title in his pivot chart to match the value label in his filter.
Dynamic Chart Titles Note: This technique will work for all Excel Charts and Chart Types. It is not restricted to Pivot Tables.
The solution is rather simple: You link the Chart Title to a formula. Link Chart Title to Formula Select a cell that is outside the range of your Pivot Table – let’s say cell H1. An example formula is: =”Sales for Fiscal Year “&D1 In this case, cell D1 contains the label for the value that you are selecting to filter your Pivot Table Report and Chart – e.g. 2008 as your Fiscal Year. Notice that in the formula, I include the “TEXT” inside ” ” (double quotation marks. I also use the & (ampersand) to join the text to the cell reference (D1). Finally, select the Chart Title and then, in the formula bar type =H1 (where H1 is the cell that contains the formula that we just wrote).
Jun 4, 2008 - Except for axis tick labels, text elements in an Excel chart can be linked to worksheet cells. These text elements include the chart title, axis titles,.
As a finishing touch, you can “hide” the formula in cell H1 by changing the FONT COLOR to match the background color for that cell. It is that easy! Try it yourself after you watch this short (6 minutes) video lesson. Invitation to Visit My New Online Shopping Site.
Hi, That was a great example using pivot charts, however I wonder if you could assist me in a dynamic chart query. I have approximately 40 sets of data, each of varying values (from 100s to 1000000s) and each set is returned in weekly values. I’m trying to set up a dynamic chart where once a user selects the name of the dataset, the length of time to display the data then excel will produce a dynamic chart. I can do this for each set, but trying to get excel to dynamically change the dataset is driving me spare. I know it can be done, but just how escapes me I don’t want to be in a situation where I’m running off 40 plus charts a week when I’m sure excel can bring this down to one chart – albeit with a lot of behind the scenes magic regards dave.
If you don't want to get lost in all the charts in your Excel worksheet, then spend some minutes to read this article and learn how to add a chart title in Excel 2013 and have it dynamically updated. I'll also show you how to add descriptive titles to the axes or remove a chart or axis title from a chart. There's nothing to it!:) You have to work a lot in Excel, make thousands of calculations and organize your data using different tables and charts.
Your mind starts spinning when you see these yards of facts and figures. No doubt that graphical data are much easier to understand. The problem is that when you create a basic chart in Excel 2013/2010, a title is not added to it by default. You have to add it manually. If you have just one chart in the worksheet you don't have to pay attention to the absence of the title.
![Chart Chart](https://cdn.extendoffice.com/images/stories/doc-excel/link-cell-to-chart-title/xdoc-link-cell-to-chart-title-3.png.pagespeed.ic.N_JITJ-eLt.png)
But your chart will look by far more attractive with it. Once several diagrams appear in your worksheet you may tie yourself up in a knot. Add a chart title Here's a very simple example how to insert a chart title in Excel 2013. This technique works in any Excel version for all chart types. Click anywhere in the chart to which you want to add a title.
Once you select the chart, the CHART TOOLS will appear in the main toolbar. You can see them only if your chart is selected (it has a shaded outline). In Excel 2013 the CHART TOOLS include 2 tabs: DESIGN and FORMAT. Click on the DESIGN tab. Open the drop-down menu named Add Chart Element in the Chart Layouts group. If you work in Excel 2010, go to the Labels group on the Layout tab. Choose 'Chart Title' and the position where you want your title to display.
You can place the title above the graphical image (it will resize the chart a little bit) or you can choose the Centered Overlay option and place the title right up on the chart and it will not resize it. Click inside the title box. Highlight the words 'Chart Title' and start typing the desired name for your chart. Now it is clear what the chart shows, isn't it? Format a chart title. If you go to DESIGN - Add Chart Element - Chart Title again and choose 'More Title Options' at the bottom of the drop-down menu, you'll be able to format your chart title.
You'll see the following sidebar to the right of the worksheet. In Excel 2010 you'll find 'More Title Options' at the bottom of the Chart Title drop-down menu in the Labels group on the Layout tab.
Another way to display the Format Chart Title sidebar is just to right-click on the title box and choose 'Format Chart Title' as on the screenshot below. Now you can add a border, fill color or apply 3-D format to the title or change its alignment. Right-click on the title box and choose the Font option or use the formatting buttons on the Ribbon ( HOME tab, Font group) to format the text.
In both cases the following window will display. Now you can change the font style, size or color of the title; add different effects to the text; modify character spacing. Make a dynamic chart title The time has come for automating the chart title. The solution is quite simple - you need to link the chart title to a cell with a formula. Click on the chart title.
Type the equal sign ( =) in the Formula bar. When you type in the equal sign, please, make sure that it is in the Formula bar, not in the title box.
Click on the cell that you want to link to the chart title. Note: The cell should have the text that you'd like to be your chart title (as cell B2 in the example below). The cell can also contain a formula. The formula result will become your chart title.
You can use the formula directly in the title, but it is not convenient for further editing. After you do that, you'll see the formula reference including the worksheet name and the cell address in the Formula bar. It is very important to type the equal sign ( =). If you forget to do it, you will just move to another cell instead of creating the dynamic Excel link. Press the Enter button. So now if I change the text in cell B2, the chart title will be automatically updated. Add an axis title A chart has at least 2 axes: the horizontal x-axis (category axis) and the vertical y-axis.
3-D charts also have a depth (series) axis. When the values don't speak for themselves you should include axis titles to clarify what your chart displays. Select the chart. Navigate to the Chart Layouts group on the DESIGN tab.
Open the drop-down menu named 'Add Chart Element'. In Excel 2010 you have to go to the Labels group on the Layout tab and click the Axis Title button. From Axis Title options choose the desired axis title position: Primary Horizontal or Primary Vertical. In the Axis Title text box that appears in the chart, type the text that you want.
If you want to format the axis title, click in the title box, highlight the text that you want to format and go through the same steps as for. But in the Add Chart Element drop-down menu go to Axis Title - More Axis Title Options and make the changes you want. Note: Some chart types (such as radar charts) have axes, but they don't display axis titles. Such chart types as pie and doughnut charts do not have axes at all so they don't display axis titles either. If you switch to another chart type that does not support axis titles, the axis titles will no longer be displayed. Remove a chart or axis title Choose one of the solutions below that works best for you to remove a chart or axis title from a chart. Solution 1.
Click anywhere in the chart. Open the Add Chart Element drop-down menu in the Chart Layouts group on the DESIGN tab. Select the Chart Title option and choose 'None'. Your chart title disappear without a trace. In Excel 2010 you'll find this option if you click on the Chart Title button in the Labels group on the Layout tab. Solution 2 To clear off the title in no time, click on the chart title or an axis title and press the Delete button. You can also right-click on the chart or axis title and choose 'Delete' from the context menu.
![Excel chart title from cell Excel chart title from cell](/uploads/1/2/5/4/125481538/416344556.png)
Solution 3 If you've just typed in a new title and changed your mind, you can click 'Undo' on the Quick Access Toolbar or press CTRL+Z. Now you know how to add, format, automate and remove such small but important details as chart and axis titles. Don't forget to use this technique if you want to make a complete and accurate presentation of your work using Excel charts. It's easy and it works!