Excel Weekly News from Contextures May 6, 2014
Click to hide pivot arrows + more Excel tips
In this week's Excel news, you'll see how to hide drop down arrows in pivot table headings, and other tips. Thank you for reading the Excel news!
- Debra firstname.lastname@example.org
- Paste With Shortcut
- Hide Pivot Table Arrows
- Warning for Grouped Sheets
- More Excel Tips
- Video: Copy Code To a Workbook
- Weekly Photo: Canadian History
- Excel Tools
Paste With Shortcut in Filtered Excel List
Last week, I showed you a couple of ways to avoid problems when pasting into a filtered list. Use those workarounds, if you are pasting data from another location. However, if you are copying and pasting data from one column to another, within the filtered list, you can use a shortcut command.
Click here to see the details, and watch the video: Paste With Shortcut in Filtered Excel List
Hide Pivot Table Drop Down Arrows
If you don't want people to filter or sort the fields in a pivot table, you can hide some or all of the field heading arrows. Change a setting, to hide all the arrows (and heading text), or use a simple macro to hide specific arrows.
Click here to read the details, and watch the video: Hide Pivot Table Drop Down Arrows
Dashboard Course and Tools
You could use the above tip, for hiding pivot table arrows, to protect the layout on a dashboard. If you'd like to improve your dashboard skills, Mynda Treacy from My Online training Hub is opening her Excel Dashboard Course, and registration ends this Thursday, May 8th.
I've been through this course, and highly recommend it. The course is video based, delivered online and is available 24/7. You'll receive comprehensive workbooks and sample dashboards to keep, and there's even an option to download the videos.
Bonus: If you sign up for Mynda's course through one of my links, I'll send you a copy of the time-saving DataPig Dashboard Tools add-in. Just enter the Referral Code – DebraD – in the box just below the Buy Now button. You can see the Dashboard Tool key features in this very short video.
Show Warning for Grouped Sheets
You can select two or more sheets in a workbook, then enter the same data, or apply formatting, to all the selected sheets. This is a real timesaver, but things can go horribly wrong if you forget to ungroup the sheet. Then, you can accidentally overwrite data on multiple sheets, and end up with a real mess!
With a bit of code, you can show a warning, if multiple sheets are selected. That might help you prevent workbook tragedies.
Click here to see the details, and copy the sample code: Warning For Grouped Sheets
More Excel Tips
Here are a few of the Excel articles that I read recently, that you might find useful:
- Instead of leaving blank cells, Mike Alexander shows how to display light grey zeros in a pivot table. This makes it easy for your eyes to follow across a row, and the light colour isn't distrcting.
- Despite the settings in default charts, Jon Peltier shows us that colours aren't needed to make data easy to read. And, instead of red and green, for bad and good results, Dr. Nicolas Bissantz recommends using red and blue.
- Jazz up your dashboard, with a bit of fancy formatting in your pivot table slicers – make them look like tabs, or even a chalkboard.
- Kurt Schmucker, who used to work on the Office for Mac team, lists the differences between Mac and Windows versions of Excel.
- Finally, for a humorous peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.
Video: Copy Code To a Workbook
If you'd like to use some of the sample code from my website, or from other sources, watch this video to see how to copy the code, and paste it into your workbook.
Some Canadian History
Last week, we visited Niagara-on-the-Lake, which is just a couple of miles from Niagara Falls. It was a grey, cloudy day, and we stopped at the monument to Major-General Isaac Brock, who commanded the British forces in Upper Canada, during the War of 1812.
Towering 185 feet, it's one of the tallest memorials in Canada, and it certainly is imposing, with the General at the top, looking out over the Niagara River.
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
- 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.