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 firstname.lastname@example.org
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:
- 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
- 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.
- 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.
- 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.
- 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.
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.
Recommended Excel Tools
In addition to all the free Excel tips and tutorials, there are other Excel tools that you can invest in. To learn more about the products listed below, 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 some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2017
All rights reserved.