Contextures

Contextures News 20180424

Excel Task List Trick

April 24, 2018

Magically cross off completed tasks, 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!

Completed Tasks

Excel can do all kinds of complicated calculations, but you probably use if for simple things too, like a list of the tasks that you have to work on.

In this example, I marked the completed tasks with an X in the "Done" column. Then I set up a simple conditional formatting rule on the entire table, to check if there is anything in column C, in each row.

=$C2<>""

If that "Done" cell has something in it, the font in that row changes to Strikethrough, with a grey font colour. That makes it easier to focus on the tasks that still need to be finished. This might slow down a large list, but is handy for small/medium sized lists.

There are more Conditional Formatting examples on my website, and this example is in the first file in the download section. If you're just getting started with conditional formatting, see the introduction page.

Conditional Formatting Strikethrough

Custom Styles

If you create a custom pivot table style in an Excel file, there isn't a built-in command to copy that style to a different workbook. You can use this workaround though:

  1. Open the workbook (A) that has a pivot table with the custom style applied.
  2. Open the workbook (B) where you want to add that custom style
  3. Arrange the workbooks, so you can see the sheet tabs in both files
  4. Press the Ctrl key, and drag a copy of the pivot table sheet from A, into B.
  5. The custom style is automatically copied to workbook B, and you can delete the sheet that you copied into it, from workbook S.

Note: In Excel 2007-2013, you can copy and paste just the pivot table, but that doesn't work in Excel 2016. Now you need to copy the entire sheet.

To learn more about custom styles, go to the Pivot Table Formatting page on my website.

insert worksheet

Excel Articles

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

Charts - Chandoo shows different ways to make an interactive chart in Excel, and I like the pivot table version, rather than all those formulas. (Level - Int)

Data Model - Susan Harkins shows how to connect tables in an Excel data model, instead of using lookup formulas (Level - Int)

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

Who Likes Owls?

My granddaughter is in Brownies, which is part of the Girl Guides of Canada (similar to Girl Scouts). The troop leaders have owl names, like Snowy Owl, so I found a stuffed owl pattern that we could work on together. I made one on my own first (be prepared!), and it turned out pretty well.

The pattern designer is the Scientific Seamstress, and the pattern is free*, , with clear instructions. Give it a try, even if you don't have an 8-year-old to help you! (*You'll have to create a free account on that site to get the download.)

weekly photo

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: http://www.contextures.com/newsletter/excelnews2018/20180424ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ gmail.com

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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: May 4, 2018 3:25 PM