Search Contextures Sites
Excel Weekly News from Contextures Feb 26, 2013
Show message at top of sheet, quick grand totals, and other tips, in this week's Excel news from Contextures.
Show Data Validation Message in Text Box
When you're setting up data validation on a worksheet, you can include an Input Message, to help anyone who's using the workbook.
However, the message is limited to 255 characters, and you can't control the font or colour.
To highlight those messages, you can show them in a text box, at the top of the worksheet.
To see the details, click here: Show Data Validation Message in Text Box
Block Selection of All in Report Filter
n some pivot tables, you might want to prevent people from selecting the "(All)" option in a Report Filter. For example, if you have formulas that use the selected item in a filter, you'd want to make sure that only one item is selected.
You can't remove the (All) option from the report filter's drop-down list, but you can use Excel VBA to block users from selecting it.
To see the details, and to download the sample file, click here: Block Selection of All in Report Filter
List All Pivot Table Formulas
If you create calculated fields or calculated items in a pivot table, you can quickly list all those formula, by using a built-in pivot table command -- List Formulas.
However, that just lists one pivot table's formulas. If you have several pivot tables in your workbook, you can use programming to list all the formulas.
Click here, to read the details: List All Pivot Table Formulas
Office 365 Launch
Instead of desktop versions of Office, Microsoft is encouraging people to subscribe to an online version of Office. Maybe it's old-fashioned, but I like to have a desktop copy of any software that I buy, instead of depending on something in the cloud.
The New York Times has written about Microsoft's new approach, and has a few quotes from Kurt DelBene, President of Microsoft's Office division.
To read the article, click here: For Rent: The New Microsoft Office
If you'd like to see a preview of Office 365, you can attend Microsoft's online launch of the Office 365 product tomorrow, Wed. Feb. 27th. There are two sessions:
- 8 AM Pacific Time
- 5 PM Pacific Time
To register, click here, and fill in the registration form: Office 365 Launch Registration
Quick Grand Total for Rows and Columns
Here's another one of my favourite Excel time-savers -- one click to total all the rows and columns in a table.
To quickly total columns and rows:
- Select the cells that you want to sum, and include blank cells below and to the right.
- On the Ribbon's Home tab, click the AutoSum button.
Here is a short video that shows how it works.
Recommended Excel Tools
They aren't free, but these Excel tools are a worthwhile investment, so please click on the links to take a look at their features, and decide if they're right for you.
- 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 the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.