Search Contextures Sites

 

Contextures
Newsletter Index

 

Contextures
Excel news
by email

 

 

 

30 Excel Functions in 30 Days

 

 

 

Contextures News

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 ddalgleish@contextures.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.

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:

  1. Select the cells that you want to copy.
  2. Point to the border of the selected range, and the pointer should change to a four-headed arrow.
  3. Press the RIGHT mouse button, and drag the cells slightly to the right.
  4. Keep pressing the right mouse button, and drag the cells back to their original location.
  5. Release the right mouse button and a shortcut menu will appear.
  6. Click on Copy Here as Values Only.

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.

Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.