Excel Flexible Formatting
January 22, 2019
Add table rows, flexible conditional formatting, 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.
To add a single row of new data
To paste new data at the end of the table
The Total row moves down automatically, as you can see in the animated screen shot below.
See more Excel Table tips on my Contextures site.
Excel has built-in Conditional Formatting rules that let you highlight the top or bottom items in a list. However, you have to type a specific number for those rules (Top 3, Bottom 5), when you apply them. Later, if you want to change that number, it takes a few steps.
To create a flexible rule, use the LARGE or SMALL function, and refer to a number on the worksheet, where it's easy to change. (There's a step-by-step video on my Contextures blog).
In the screen shot below, I selected cells C2:C13, and applied this conditional formatting rule, with orange highlighting:
Later, type a different number in cell E2, and that many cells are highlighted. Or, type a zero, to temporarily remove the highlighting.
And for more CF examples, go to the Conditional Formatting Examples page on my Contextures site.
If you have a copy of my Pivot Power Premium (PPP) add-in, check out the new Help features:
Here are a few Excel articles that you might find useful or interesting.
Macros - Is 2019 the year you'll learn how to automate Excel? Jon Acampora is running free webinars this week that shows you how to get started with Excel macros. There's a full course too - VBA Pro - if you decide to learn even more about macros. (Level - Int/Adv)
Power BI - Another topic to explore in 2019 is Power BI. Reza Rad just posted part 2 of his excellent series on Getting Started with Power BI Desktop. And here's Part 1, if you missed it. (Level - Int/Adv)
Functions - This paper is definitely not for most Excel users, but you might find it interesting. It's from 2003, and the authors wanted Microsoft to add worksheet-based User Defined Functions in Excel. See the screen shots on pages 4 and 7, for examples. (Level - Adv)
Thanks for sending your Q&A stories in reply to last week's photo - it was great to hear your memories! Someone mentioned Q&A manuals - do you remember the thick books that came with most software? (I've still got Excel 3.0 manuals.) Now our software is "in the cloud" and Google is our help!
One of the strangest help features that I've ever seen was this cassette tape, "A Guided Tour of Macintosh", that came with our original Mac, in 1984. Ah, the good old days!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190122ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: January 22, 2019 10:09 AM