Contextures

Contextures News 20180814

Excel Slicer Setup Tip

August 14, 2018

Slicer setup tip, comment macros, 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: You'll get the next newsletter on August 28th -- 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.

Comment Macros

If you add comments to worksheet, eventually those comments might wander away from their original locations. Sometimes the comments change size too, and you need to adjust them. Instead of making changes manually, move or resize Excel comments with macros, to save time.

In the past, I posted macros to add comments, format or number them, change the author name, and other exciting things! You can see those comment macros on my Contextures site.

comments with numbers

Now there’s a new page with comment macros, contributed by J. Woolley. You can download the sample file on that page, to see the code and test the macros. There's even a macro that changes the text on the message box buttons! For example, this message has Sheet, Selection and Quit buttons, instead of Yes, No and Cancel.

message box buttons

Slicer Setup Tip

Excel Slicers make it easy to filter the data in a pivot table or named Excel table, so it's nice to have them on your worksheets. The disadvantage of using Slicers is that they can take up a lot of room!.

Sometimes, I freeze the first row or column on the sheet, and put the slicers there, but that doesn't work for every situation. For Slicers with lots of items, here's a tip from UniMord.

  • Cut the Slicers, and paste them onto a different worksheet.
  • Then, open a new window in your workbook, and select the Slicers sheet in that window.
  • Make the new window narrow, to just fit the Slicers
  • Then arrange the windows, so you can see the Slicers at the left, and the data window at the right.

NOTE: To use the Slicers, click once in that window, to activate it, then click on one or more of the Slicers, to filter the data.

slicer setup

Read more about Slicers, and see how to add them, on my Contextures website. There are Slicer macros too, that let you update your Slicers.

Excel Training

Here are two great opportunities to improve your Excel skills.

Excel Dashboards - From now until Thursday (Aug. 16th), Mynda Treacy is hosting free webinars on how to build effective Excel Dashboards, using normal Excel features, or Power Query features. Choose the session of interest to you, or sign up for both!

Registration is also open for Mynda's highly-rated Dashboard course, if you need to improve your dashboard skills to the advanced level. There is an early-bird discount of 20% off, if you register by August 16th. Click here for the Excel Dashboard Course or the Power BI Course

Excel University - If you need to create reports in Excel, which is better -- Pivot Tables or Formulas. Jeff Lenning, from the Excel University website, will look at the pros and cons of each method, in free webinars this week (Aug 14-16). Jeff is also opening the registration for his Excel University Graduate Certification program, until August 24th. The program is only available twice a year.

Excel Articles

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

Dashboards - On the Journal of Accountancy blog, J. Carlton Collins shows how to create a dashboard with Excel's Camera tool. But please don't use 3-D charts, like the ones in the example! (Level - Int)

Data Model - Rick de Groot has a good lesson on how to use the Data Model in Excel. There are lots of screen shots, and clear explanations. (Level - Int)

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

Paper Weaving

Last week, I borrowed a book about paper weaving from our city's online library, and we tried a few of the "easiest" patterns. It was fun, but the instructions were short on details in a few places. This page has a similar woven paper heart -- maybe you'll find it easier to follow than I did!

Struggling to understand those instructions made me appreciate how important it is to clearly describe all the steps, when I'm writing Excel tutorials!

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: https://www.contextures.com/newsletter/excelnews2018/20180814ctx.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: August 10, 2018 3:54 PM