Contextures News 20180918

Excel Cell Format Trick

September 18, 2018

Pivot table drilldown, formatting trick, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Pivot Table Drilldown

When you double-click on a pivot table value, Excel creates a new sheet, with the records that were summarized for that value. That feature is called Show Details, or Drilldown.

To help keep track of those sheets, I've made macros to name the sheets, and delete some or all the drilldown sheets when you close the workbook.

Get the sample files on my website -- there is a basic version, and a newer version that lets you customize the macro settings. The basic version macros are described in the tutorial on that page.

Fill a Cell

If you use Excel to create printable reports, such as order forms, you might need lines for signatures.

Instead of using a cell border to create a line, or typing a bunch of underscore characters, here's a quick way to fill a cell with one or more characters.

1. Select the cell, and type an underscore (or other characters)
2. Press Ctrl+1, to open the Format Cell dialog box
3. On the Alignment tab, click the arrow for Horizontal alignment, and choose Fill
4. Click OK, to close the dialog box

The cell is filled with the character(s) that you, and automatically adjusts if you change the column width.

There are more Cell Formatting tips on my website.

Excel Articles

Here are a few Excel articles that you might find useful or interesting.

Power Query - On his Excel Guru blog, Ken Puls is doing Power Query challenges, and the third challenge just closed, so watch for the solutions soon. In the meantime, see #2 solutions, to get ideas for your own projects. (Level - Int/Adv)

Macros - Jon Acampora shows two ways to assign keyboard shortcuts to your macros, and lists the pros and cons of both methods. (Level - Intermediate)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

In the Country

Mississauga is Canada's 6th-largest city, but we're on the northern edge, just a few minutes from farm country. Last week, we had lunch at a hilltop restaurant, and in my photo, you can see the green fields in the valley below. We'll head back to that area next week, to one of the many apple orchards, and get a few pecks of our favourite varieties. This is the start of peak season for the apple varieties available in our region, including Bubble Gum!

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180918ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
[email protected] gmail.com

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