Search Contextures Sites
Excel Weekly News from Contextures September 16, 2014
Clear cells + more Excel tips
In this week's Excel news, you'll see how to automatically clear a cell, and much more. Thank you for reading the Excel news, and I hope that you are enjoying the Excel articles. If you have suggestions for future topics, please let me know.
- Debra - firstname.lastname@example.org
- Macro Clears Dependent Cell
- Slicers for Conditional Formatting
- More Excel Tips
- Excel Announcements
- Video: Count Function Quirks
- Weekly Photo: Wine for Excel Fans
- Excel Tools
To help people enter data in a worksheet, you can set up drop down lists, where they can select items, instead of typing. It's also possible to make one drop down list dependent on what was selected in another drop down.
To prevent mismatched selections, you can use a macro to clear the dependent cell, when a new selection is made in the first cell.
Click here to see the details: Macro Clears Dependent Cell
I'd never thought about using Slicers to control conditional formatting, until I watched a short video by John Michaloudis, from My Excel Online. Instead of fussing with the settings, just click a Slicer button, to change the values. It would be great on a dashboard, or anywhere else that you want to make it easy for people to adjust things.
John uses the technique with a pivot table, but you could also do this with regular cells on a worksheet. You could even use a Slicer to change values for a formula, for something like a budget or forecast.
Click here to watch the video, and download the sample file (scroll down to find the videos): 10.13 Control Conditional Formatting with Slicers
The video is from John's Xtreme Pivot Table course, which has more than 200 videos, along with practice workbooks, finance business cases, and 12 months of personal support. You can use the coupon code CONTEXTURES for a 10% discount.
Here are a few of the Excel articles that I read recently, that you might find useful:
- Did you know about the data entry shortcut that Gasper Kamensek discovered, while teaching a basic Excel class? When I checked the survey results, all the respondents had picked “No” (including me).
- Take a look at the amazing dashboard that Robert Mundigl built, featuring an animated chart.
- It was Teacher’s Day this month, and Chandoo asked his readers to post their favorite Excel tips, in honor of the occasion.
- Ken Puls is happy about the new Merge Columns feature in Power Query. Maybe they’ll add that in Excel one day.
- Donna Gilliland uses conditional formatting and Sort by Color to group text entries in a worksheet.
- Finally, for a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
Here are some upcoming events, courses, recently published books, and other new items, related to Excel:
Excel VBA for Financials, 2-day course (in Dutch), led by Excel experts, Jan Karel Pieterse and Tony de Jonker. This course is specifically aimed at the Finance professional. It has no frills, and no technical mumbo-jumbo – just the VBA you need to achieve more success in your daily job.
Date: November 19 and December 3, 2014. Location: Utrecht (Netherlands)
You can use Excel count functions to count cells with numbers only, count cells with any type of data or count blank cells. The video shows the COUNT function, COUNTA function, COUNTBLANK function
Even if you don't drink wine, you might like the label, "The Formula", on this bottle -- it seems perfect for Excel fans.
I tested it last week, just for scientific purposes, and would definitely have it again.
In addition to all the free Excel tips and tutorials, there are other Excel tools that you can invest in. To learn more about the products listed below, click on the links to take a look at their features, and decide if they're right for you.
- Xtreme Pivot Table course, use coupon code CONTEXTURES for a 10% discount.
- Contextures Excel UserForm Course
- Contextures Excel Tools Add-in
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- FastExcel V3
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.