Contextures News 20190122

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.

Add Table Rows

If your data is in a formatted Excel table, do you usually show a Total row, like I did in last week's Subtotals tip? If so, you don't need to hide that Total row, when you want to add new data.

To add a single row of new data

  • Select the last cell in the last row of data
  • Press the Tab key, to start a new row

To paste new data at the end of the table

  • Copy the data that you want to add (Ctrl+C)
  • Select the first cell in the Total row, even if there‚Äôs text or a formula there
  • Then, press Enter, or Ctrl+V, to paste the copied data

The Total row moves down automatically, as you can see in the animated screen shot below.

paste rows at end of table

See more Excel Table tips on my Contextures site.

Flexible Conditional Formatting

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:

  • =C2<=SMALL($C$2:$C$13,$E$2)

Later, type a different number in cell E2, and that many cells are highlighted. Or, type a zero, to temporarily remove the highlighting.

flexible conditional formatting

And for more CF examples, go to the Conditional Formatting Examples page on my Contextures site.

Excel Articles

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)

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

Guided Tour

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!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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.

Debra Dalgleish
[email protected]

Debra Dalgleish

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.


Last updated: May 24, 2021 8:42 PM