Excel Weekly News from Contextures Nov 19, 2013

Find external links + more Excel tips

In this week's Excel news, you'll see how to fix external links with a free add-in, and other tips. Thank you for reading the Excel news!

 -  Debra ddalgleish@contextures.com

Get Latest Price for Specific Product

Use the MAX and IF functions to find the latest date that a product's price was changed. Then, using the product name and that date, you can find the latest price for the product – even if it isn't the highest price.

Click here to see the details, and get the sample file: Get Latest Price for Specific Product

Change Selected Pivot Fields to SUM

When you create a pivot table, it automatically makes the value fields either SUM or COUNT, depending on the types of data in those fields. You can manually change the summary function, or use a macro. In this example, you can select specific fields, and only change those to SUM, leaving the other fields as is.

Click here to see the details, and download the sample file: Change Selected Pivot Fields to SUM

Find External Links in an Excel File

When you open an Excel file, you might see a warning about links in the file. If you want to find those links, you can follow a few steps to locate them. Or, download a free add-in that will make the job easier.

Click here to see the details: Find External Links in an Excel File

More Excel Tips

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

  1. Do you ever use the Window key on your computer keyboard? Chandoo shows how to use it as a quick way to paste as values
  2. Do formulas that refer to their own sheet calculate faster than formulas that refer to other sheets. Charles Williams runs a few tests, and is surprised by the results.
  3. If your data isn't set up correctly, you won't be able to build a flexible pivot table. Microsoft's Power Query add-in makes it easy to “unpivot” your data, and Ken Puls shows the steps on his Excel Guru blog.
  4. For the perfect Christmas (or Thanksgiving) meal, be sure to use a spreadsheet! See the Sainsbury's commercial with a spreadsheet here, and you can download my holiday meal planner here.
  5. 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: Add New Items to Drop Downs

To make data entry easier, you can create drop down lists on the worksheet. If you want to allow people to add new items to those drop down lists, you can use a bit of programming (copy from my sample file).

This short video shows how the technique works.

weekly video

Pink and Orange

Most of the trees have lost their colourful leaves, but there are still a few surprising bright spots along the walkways. Last week, I saw these delicate pink and orange clusters, and thought they were flowers. With a little research, I learned that they are the fruit of a spindle plant, originally from Europe, and now invading North America.


Contextures Products

Save time with our "Done For You" Excel products.


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




Privacy Policy


Contextures Inc., Copyright ©2019
All rights reserved.