Contextures News 20180403

Excel Quick Format Trick

April 3, 2018

What's hidden on your worksheet, 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 supports the free info on my site!

Quick Formatting

Last week, I helped a friend with a worksheet, and we set up some complex formatting in one section. Three other sections needed the same formatting, so here are the steps to quickly apply it:

• Select the formatted cells in the first section
• On the Excel Ribbon's Home tab, double-click the Format Painter, to select and "Lock" it
• On the worksheet, click the first cell to be formatted in the next section
• Then, click the first cell in the each of the remaining sections
• Click the Format Painter again, to "Unlock" it (or press the Esc key)

My friend has used Excel for years, but had never seen that Format Painter trick. She loved it, and shared it with her co-workers - it's a real timesaver!

There are more double-click tricks on my Contextures site -- do you use any of them?

What Is Hidden?

Have you seen the articles that blame Excel for all kinds of business errors? In some cases, problems occur because rows were hidden, and that distorted the data analysis.

To help avoid those problems, I created a sample file that shows warnings, if rows or columns are hidden. There's a formula to check for hidden rows, whether they're hidden manually, or by a filter. The formula counts numbers in the OrderCount column, then subtracts the count of visible numbers, using the AGGREGATE function.

=COUNT(Table1[OrderCount]) - AGGREGATE(2,5,Table1[OrderCount])

Unfortunately, AGGREGATE doesn't work for hidden columns, so I used the CELL function to check the cell widths. On the worksheet, the results didn't automatically update if columns were hidden or unhidden. But, as a conditional formatting rule in cells A1:J1, it seems to work nicely.

=CELL("width",B1)=0

A cell turns yellow, if the cell to its right is hidden (0 width). In this screen shot, 2 rows are hidden, and columns E and F are hidden.

See more conditional formatting examples on my Contextures Blog.

Excel Articles

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

Data Viz - Ann K Emery is offering a free 7-day online course that shows how to improve your reports with better data viz and dashboards. You'll see report makeovers from a variety of industries, and can apply the techniques to your own work. (Level - Int)

Macros - Jon Acampora is hosting free webinars next week - "7 Steps to Getting Started with Macros & VBA". Jon explains how you benefit from learning VBA, and how to get started. There are 2 webinars each day, from Mon, April 9th to Fri, April 13th. Click here to pick a date and time that works for you. If you want to go beyond the basics, Jon also has a full macro course. (Level - Intermediate)

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

Pranks

Our family came for Easter brunch on Sunday, and since it was also April Fool's Day, our grandkids played a prank on us. They wrote silly things on sticky notes, and covered the upstairs television with them. That gave us a good laugh when we found the notes later, hours after everyone had gone home.

I saw a few April Fool's pranks online too, and my favourite was this article announcing that DevScope would release a Clippy add-in for Power BI. If it came with those funny comments, I'd use it!

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/20180403ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @ contextures.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.