Search Contextures Sites

 

Contextures
Newsletter Index

 

Contextures
Excel news
by email

 

 

 

30 Excel Functions in 30 Days

 

 

 

Contextures News

Excel Weekly News from Contextures May 14, 2013

Move a large pivot table + more Excel tips

It was a cool, wet weekend, in my neighbourhood, so I hope it was nicer where you live. You can see the grey skies in my photo this week, at the end of the newsletter.

In this week's Excel news, you'll see how to calculate annual savings, move a large pivot table, and other tips. Thank you for reading the Excel news!

 -  Debra ddalgleish@contextures.com

Calculate Annual Costs and Savings

Your small expenses can quickly add up to a large amount, and with this Excel template, you'll see the annual cost of those regular expenses.

You can also enter your plan for cost cutting, and the template will show you the annual totals, and your potential savings, if you can make those cuts.

Click here to see the details and to watch the short video: Calculate Annual Costs and Savings in Excel

Quickly Move a Large Pivot Table

Instead of struggling to cut and paste a large pivot table, you can use a Ribbon command to quickly move an Excel pivot table to new sheet.

Click here to see the details: Quickly Move a Large Pivot Table

Get Mileage from Excel Lookup Table

You've probably seen lookup tables that show the distance between cities -- find a city name at the left, and another city name at the top, and the distance between them is shown at the intersecting point in the table.

If you create a similar lookup table in Excel, you can use the INDEX and MATCH functions to calculate the mileage between two selected cities. Here’s the mileage lookup table in my sample file, with cities in Florida.

Click here to see the details, and download the sample file: Get Mileage from Excel Lookup Table

More Excel Tips

Here are a few more Excel articles that I read this week, that you might find useful:

  • In his Not Just Numbers blog, Glen Feechan offers his Ten Principles for Excel Good Practice. You'll find some sound advice in this list, and check the comments too.
  • For a peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.
  • With conditional formatting and the camera tool, Jorge Camoe has created a choropleth/thematic map in Excel, without any programming. It would take patience to set this up, but his technique might give you ideas for smaller projects.
  • If you import text files in Excel, take a look at Dick Kusleika's article on importing fixed width text files, and his attempt to improve on Excel's Text Import Wizard. After that attempt, he switched to using ADO.

Video: Quickly Clear Data Entry Cells

If a worksheet has a mixture of formulas and data entry cells, here is a quick way to clear out all the data, and leave the formula cells untouched. Then you'll be ready to start fresh, and enter a new record.

For more data entry tips, please visit my Contextures website: Data Entry Tips

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.

Grey Skies

After a week of sunny weather, the skies turned grey, just in time for the weekend. We walked to the nearby playground, but it was too wet and chilly to stay for very long. I liked these sycamore leaves though, against the grey sky.

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright ©2014
All rights reserved.