Have a Good Weekend

I hope you are having a good weekend and that you enjoy the tips in this week's newsletter!

—Allen
     

ExcelTips (ribbon) for 17 August 2019

Formulas
Strange Value Results in a Cell

When Excel performs a calculation, the results you see in an unformatted cell may cause a bit of concern. This tip explains why you may see the concerning results and what you can do about them.

Read this tip »

(Thanks to Steve Aprahamian, Harold Druss, Ron Solecki, Benjamin Morin, Elliot Penna, Wayne Edmondson, Ron Breen, and Joe Balogh for contributing to this tip.)

 
Protecting workbooks
Protecting a Workbook from Opening in Other Programs

When working with sensitive information, you always need to be concerned that your data doesn't fall into unwanted hands. With the number of programs growing that can actually open Excel workbooks, the problem becomes even more critical.

Read this tip »

(Thanks to Shreepad Gandhi for contributing to this tip.)

 
PivotTables Make You Feel Faint?

Do you get weak in the knees when asked to deal with huge amounts of data? Forget feeling faint ever again when you are asked to deal with PivotTables. Learn much more than the basics with PivotTables for the Faint of Heart, now available in three great versions.

 
Formulas
Using Named Formulas Across Workbooks

You can use the naming capabilities of Excel to name both ranges and formulas. Accessing that named information in a workbook external to your current workbook can be a challenge. Here's how to approach the problem.

Read this tip »

 
Worksheet functions
Exact Matches with DSUM

The DSUM function is very handy when you need to calculate a sum based on data that matches criteria you specify. If you don't specify the criteria just right, however, you may not get the results you expect.

Read this tip »

 
Macros extend Excel
Removing Pictures for a Worksheet in VBA

Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time figuring out how to get rid of the pictures. Here are some ideas.

Read this tip »

 
Online and web
Specifying Default Hyperlink Text

When you insert into a cell a hyperlink that references a file on your system, the text displayed by default matches the path and filename. If you want to change that default, you are out of luck, but there are workarounds.

Read this tip »

     

Help Wanted

This section is for those having problems making Excel behave. If Excel is giving you fits, feel free to submit your own Help Wanted question.

If you have a solution for the problems below, click the link after the problem to send us your answer. (All responses become the sole property of Sharon Parq Associates, Inc., and can be used in any way deemed appropriate.) If your response is used in a future issue, you will be credited for your contribution to the answer.

 
Limiting Where a Workbook is Used

I work with a volunteer organization and we have developed an Excel workbook specifically for our volunteers. We don't want it copied or shared with outside individuals. Is there a way to protect the workbook so that it will work only for a specific computer and no others?
—Shay Raphael (provide an answer for this Help Wanted question)

 
ExcelTips is a free service of Sharon Parq Associates. You can find thousands of tips at our website and a whole lot more. ExcelTips is part of the Tips.Net network.
Sharon Parq Associates, Inc.  •  PO Box 1187  •  Mountain View, WY  82939  •  307-200-0450