Contextures News

Protected Pivot Tables and Name Fixes

March 28, 2017

Free Data Analysis webinars tomorrow (March 29th), and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Name Fix Solutions

Last week's challenge was to clean up a simple list of names, using Excel formulas. Thanks for sending your solutions, and as always, it was fun to see how you solved the problem.

  1. Show the names in the correct upper and lower case. Most people used PROPER: =PROPER([@Name])  OR  =PROPER(A2)
  2. Put an X in column C, if the name had been corrected. Most people used IF and EXACT: =IF(EXACT([@Name],[@Fixed]),"","X")
  3. Show a count of all the changed names. Almost everyone used COUNTIF: =COUNTIF(Table1[Changed?],"x")

To see the different solutions, including some very interesting approaches, download the completed Name Case Solution workbook. The zipped file is in xlsx format, and does not contain any macros.

fix the names challenge

Pivot on Protected Sheet

If you protect a worksheet that has a pivot table, you can still use it, but with several limitations. There's a video on my website that shows what to do when you're protecting the sheet, and there is a list of the key limitations.

For example, you can't refresh a pivot table if its sheet is protected. And, if other pivot tables are based on the same pivot cache, you won't be able to refresh them either!

To get around that problem, I've written a couple of macros that temporarily unprotect the sheet, refresh, and turn on the protection again. Download the sample file that has test pivot tables, and the macros.

Excel Articles

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

Data Analysis - Tomorrow, March 29th, register to watch the 5 free online sessions in the Business Analytics Marathon, from PASS. The sessions will focus on Data Driven Storytelling and Data Discovery. The sessions run from 17:00 to 21:00 GMT, and you can click the iCal link to add to Outlook and convert that to your own time zone. (Level - Intermediate/Advanced)

Programming - Mike Alexander explains how he organizes his VBA code in Excel, and one of the comments is from RubberDuckVBA -- a free, open source add-in for VBA, which looks interesting. (Level - Intermediate/Advanced)

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

Excel Products

Tree Decorations

Spring has sprung, but the trees are still a dreary brown -- no leaves or buds yet. This Sycamore stands out from the rest though, with its seed balls that look like decorations. I hope things start blooming soon!

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

ddalgleish @

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.




Get weekly Excel tips from Debra






Last updated: May 24, 2021 8:38 PM