Auto color cells + more Excel tips
Excel Weekly News from Contextures September 30, 2014
In this week's Excel news, you'll see how to automatically color data entry cells, 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
- Show Color With Conditional Formatting
- Get Total from Specific Pivot Table
- New Project Manager Course
- More Excel Tips
- Excel Announcements
- Video: Copy and Paste Problems
- Weekly Photo: Leaves of Many Colours
- Excel Tools
Show Color With Conditional Formatting
On a data entry sheet, select a number of options from a drop down list. That number of options show up in the data entry area. Then, select a color for each item, from another drop down list. In the cell to the right, the selected color is shown..
Click here to see the details, and watch the video: Show Color With Conditional Formatting
Get Total from Specific Pivot Table
If you have multiple copies of a pivot table in a workbook, on different sheets, you can use the GETPIVOTDATA function to pull the total amount from a specific pivot table.
Click here to see the details, and watch the video: Get Total from Specific Pivot Table
New Project Manager Course
I've known Doug Hong online for a few years, and enjoy his Excel videos on YouTube (the DougH channel). Doug has just created a new online course -- Excel 2010 for the New Project Manager. The course is on the Udemy website, and the regular registration fee is $99.
Doug is giving my newsletter subscribers an exclusive coupon for free registration, if you sign up by October 7, 2014. If you take the course and find it helpful, please leave a review -- there is a review/feedback tool on the course's web page.
For details on the content, you can watch the course introduction video, before you register. The course covers the basics of Excel 2010, and some of the features that you'll need for project management.
Excel 2010 for the New Project Manager Course -- free registration link -- expires Oct 7, 2014
More Excel Tips
Here are a few of the Excel articles that I read recently, that you might find useful:
- Kyle LeFevre would appreciate your input in his keyboard shortcuts survey (it's done in a Google form)
- Sumit Bansal shares a tip for automatically increasing the tab numbering when you make a copy of a sheet.
- Microsoft has made it easier for students and teachers to get a free Office 365 license.
- To enhance your dashboard, use the tips from Gašper Kamenšek, to show the Top 5 or Bottom 5 results, by clicking a radio button.
- If you do any programming in Excel, read Jon Acampora's tips for using the Immediate window.
- Bill Jelen (Mr. Excel) shows how to look for 3 specific names, in adjacent cells of a lookup table, by using an array formula that I hadn't seen before. Watch the video from 1:15 to 2:45, if you're pressed for time.
- 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:
- New Book: Practical Numerical Methods for Chemical Engineers: Using Excel with VBA by Richard A. Davis “over 100 VBA macros for extending Excel's power for engineering & scientific analysis. Engineers & scientists will find the enhanced coverage of computational tools applicable to a variety of problems in their own disciplines.”
2-Day Course (Dutch): 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)
Video: Copy and Paste Problems
You can run into problems if you copy multiple ranges, and try to paste them to a new location. You might see an error, "That command cannot be used on multiple selections", or values might be pasted, instead of formulas.
See the steps for copying and pasting multiple selections in this short video tutorial.
Leaves of Many Colours
We went for a walk in a nearby conservation area on Friday, and the leaves are starting to change colours. The water in the small lake was very still, and made a perfect reflection of the trees. Soon, I'll have to bundle up to stand outside, so I'll enjoy this beautiful weather while it lasts.
Recommended Excel Tools
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.
- 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 ©2017
All rights reserved.