Search Contextures Sites
Excel Weekly News from Contextures Feb 19, 2013
Round to a nickel, print new invoices, and other tips, in this week's Excel news from Contextures.
- Debra email@example.com
Round to a Nickel in Excel
The penny isn't being circulated in Canada anymore, so cash transactions will be rounded to the nearest nickel. To do this in Excel, you can use the MROUND function.
In this example, the sales total is $4.86, and it is rounded down to $4.85, by using 0.05 as the multiple argument in the MROUND function.
To see the details, click here: Round to a Nickel With MROUND
Click Button to Print New Invoices
There is a new version of the Print Invoices sample file on my Contextures site, in which you can enter invoice details, then print all the new invoices by clicking a button.
The button runs a macro that checks the list of invoices, and prints a copy of each new one. After printing, the invoice record is marked with an "X", so it doesn't get printed the next time.
To see the details, and to download the sample file, click here: Print Invoices With Excel Macro
Refresh Pivot Table on Protected Sheet
When a worksheet is protected, you can allow people to use a pivot table on that sheet. However, even when pivot table use is allowed, you won't be able to refresh the pivot table. You can't even refresh a pivot table if it is on an unprotected sheet, but other pivot tables, using the same pivot cache, are on protected sheets.
To refresh the pivot table, you will have to unprotect the sheet, refresh, and the protect the sheet again. You could do this manually, or use a macro.
Click here, to read the details: Refresh Pivot Table on Protected Sheet
Distinct Count in Excel 2013 Pivot Tables
On his blog, Mike Alexander show how we can finally get a distinct count in a pivot table, in Excel 2013, thanks to the new Data Model feature.
For example, in a list of orders, there might be thousands from customers in California, and a pivot table can show the count of those orders.
But, each order doesn't come from a different customer -- most of the customers placed several orders. With the Distinct Count function, you can see how many distinct customers are in California.
To see Mike's article, click here: Distinct Count in Excel 2013 Pivot Tables
And for older versions of Excel, you can try one of the workarounds on my Contextures Blog: Unique Count in a Pivot Table
Paste as Values Mouse Shortcut
One of my favourite mouse shortcuts is the "wiggle", that lets me copy cells, and paste them as values -- either into the original cells, or in another location on the worksheet.
To quickly copy and paste as values:
- Select the cells that you want to copy.
- Point to the border of the selected range, and the pointer should change to a four-headed arrow.
- Press the RIGHT mouse button, and drag the cells slightly to the right.
- Keep pressing the right mouse button, and drag the cells back to their original location.
- Release the right mouse button and a shortcut menu will appear.
- Click on Copy Here as Values Only.
Here is a short video that shows how it works.
Recommended Excel ToolsThey 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
- Excel Pivot Table Intro Course
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.