Contextures

Contextures News

Excel Chart Title Trick

August 23, 2016

Chart title trick, and more, in this week's Excel news. During the summer months I'll send Excel news once every two weeks, so you will get the next news on September 6th. My Excel website is always open though!

Chart Title Trick

When you create a chart, it usually gets a title with generic text, such as "Chart Title". You could click in that title, and type something more descriptive, such as "Region Sales".

Or, instead of typing the test, link the chart title to a cell on the worksheet. Then, if you change the text in the cell, the chart title will update automatically. Here are the steps:

  • Type the Chart Title text in a worksheet cell (A1 in this example)
  • Click on the chart title, to select it
  • Click in the Formula bar, and type an equal sign
  • Click on cell A1 -- the sheet name and cell address will appear in the formula bar
  • Press Enter, to complete the formula

There's another example on my blog, and an animated screen shot that shows the steps. And if you need to make complex charts, take a look at Jon Peltier's Excel Charting add-in.

chart title trick

Fix Data For Pivot Table

Before you can build a flexible pivot table, you might need to rearrange the data. For example, if the data has 12 months of data in each row, it won't work well for a pivot table. You need a single row per month.

To quickly fix the data, you can use the Get & Transform tool, in newer versions of Excel. Even if you don't like Power Query, you might like this feature!

  • On the Ribbon's Data tab, click From Table
  • Select all the columns that you want to put into a single field -- the month columns in this example
  • Right=click one of the selected column headings, and click Unpivot Columns
  • Click Close and Load, to return the data to a new Excel worksheet

Easy! Then build your pivot table from the new table.

You can see the steps and screen shots on my blog. Or, for older versions of Excel, you can use a macro to Unpivot the data for a pivot table.

number formatting shortcuts

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Retirement Planning - On the Mad FIentist blog, you can download a sample file for retirement planning -- it's never to early to start! (Name and email address required) The file is in Excel xls format, and it's interesting to see how it's set up. Enter data in the blue cells every month. (Level - All)

Find Duplicates- On the TechRepublic blog, see different ways to find duplicates in an Excel list. Use a filter to show unique values only, or highlight duplicates with conditional formatting. To filter for duplicates in a specific column, select that column, instead of the entire list. See my Advanced Filter page for more examples. (Level - Basic/Intermediate)

More Excel Articles -- Visit my Excel website for many more tutorials and videos. And for a bit of fun, read my weekly collection of entertaining Excel tweets.

Also, see all my Excel products on my Contextures website.

How Much Colour?

Last week my grandkids visited, and we did some artwork. At the left, you can see my canvas -- I taped off sections to paint. In the middle, my grandson took a minimalist approach, with a few shapes and plenty of white space. My granddaughter did the opposite -- she filled every square inch with rainbows.

Have you noticed that people take a similar approach to Excel workbooks? Some are carefully organized, others are bare bones, and a few are bursting with colour!

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

Debra
ddalgleish @ contextures.com

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: March 15, 2017 11:49 AM