Contextures News 20190730

Excel Find Locked Cells

July 30, 2019

Text trick, highlight locked cells, 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.

You'll get my next newsletter in 2 weeks -- we're on the summer schedule now.

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

Locked Cells

Do you like the new Ribbon icons in the latest version of Excel? My favourite commands are on the Quick Access Toolbar (QAT), and it took me a while to identify some of those tiny new icons.

If you're building a data entry sheet in Excel, the Lock Cell command is handy -- you can quickly see if the active cell is locked, and turn the lock on or off. By default, all cells have Lock turned on, and if you protect the sheet, the cell lock is activated.

You can also use Conditional Formatting to show which cells are locked or unlocked. The CELL function can check a cell's Protect setting, and return a zero for unlocked, and 1 for locked.

• Select the cells to format - B1:B4 in this example
• On the Home tab, click Conditional Formatting, then New Rule
• For Rule Type, click Use a Formula to determine which cells to format
• In the formula box, type this formula to check if cells are unlocked:
  • =CELL("protect",B1)=0
• Click the Format button, and choose a Fill colour for unlocked cells
• Click OK twice, to apply the Conditional Formatting

See more Conditional Formatting examples on my Contextures site.

Order Forms

If you store data in Excel, you might need a fancy way to print out some of the data. For example, enter order information, and then print out the order details on a nicely formatted sheet.

There's a simple example on my website, with a list of orders, each with one item. Type an X beside the orders that you want to print, and a macro prints each one in an order form layout.

I've uploaded a new sample file, which lets you enter up to ten items for each order. The new macro asks if you want to print or preview, and whether to clear the X marks.

Click here to download the multi-item order form sample file, to see how it works. The zipped file is in xlsm format, and contains macros

Excel Articles

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

Gantt Chart - Leila Gharani shows how to track a project's progress by setting up a Gantt chart in Excel. (Level - Int)

Text Trick: Here's a cool text trick from Steve Collins - spread text from one cell over multiple rows (up to 255 characters). Watch his short video to see how the trick works - it's not the Justify option from the Alignment settings. (Level - All)

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

Greatest Toys

Did you ever have a Skip-It toy? My granddaughter is visiting this week, and spends hours playing with hers. It's good exercise, and better than sitting at a computer all day, like some of us do!

Apparently these toys were popular during the 1980s and 1990s, and I was surprised to see that they're on the Time Magazine list of 100 greatest toys. You can check out the list, to see which toys from your decade are included.

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190730ctx.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.

Debra Dalgleish
[email protected] 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.