Check Out the Course

If you haven't taken the advantage yet of checking out my new PivotTables for the Faint of Heart course, you really should do so. Scores of people have already signed up and are discovering new ways to analyze their Excel data. (They are learning not just about PivotTables, but also about slicers, timelines, and PivotCharts--it's all in this single course!)

I'm still accepting new students through this next Wednesday (February 19), but time is racing by quickly. If you want to expand what you can do with Excel, now is the time to act. You can find out full information here:

https://usingoffice.com/pivottables/

I'll remind you a time or two again, closer to the closing, so that you don't miss out on this opportunity.

Let me know what you think!

—Allen
     

ExcelTips (ribbon) for 15 February 2020

Printing
Printing in Black and White and Color

Excel can print your worksheets in either black and white or color. If you want to print everything in black and white except for a single worksheet, then you can use a macro to do your printing for you, as described in this tip.

Read this tip »

(Thanks to Steve Aprahamian, Harold Druss, Charles O'Neill, Henk Haringsma, Byron de Vries, Wayne Edmondson, Bob Beechey, Ann Carrigan, and Patrick Verhaeghe for contributing to this tip.)

 
Drawing objects
Flipping a Drawing Object

Don't like the way a drawing object looks? Perhaps flipping the object could help appearances. Excel allows you to flip drawing objects either horizontally or vertically.

Read this tip »

 
Formatting Made Powerful

Discover one of the foundational building blocks of Excel. Custom formats are the basis for all cell-level formatting in Excel, and you can master their use. Display your data exactly and precisely as you want.

 
Formulas
Calculating Monthly Interest Charges

Trying to calculate how much people owe you? If you charge interest or service charges on past-due accounts, there are a myriad of ways you can calculate balances. Here's a discussion on a few ways you can try.

Read this tip »

 
Worksheet functions
Using GEOMEAN with a Large List

When performing a statistical analysis on a large dataset, you may want to use GEOMEAN to figure out the geometric mean of the data. Doing so, however, could possibly generate an error, as described in this tip.

Read this tip »

 
Macros extend Excel
Workbook Events

You can create macros that run whenever Excel detects a certain event happening within an entire workbook. This tip explains how this occurs and explains how to create the macros associated with those events.

Read this tip »

 
Formulas
Extracting a State and a ZIP Code

Excel is often used to process or edit data in some way. For example, you may have a bunch of addresses from which you need to extract information, such as a state and ZIP Code. This tip examines one such scenario and how you can get the information you need.

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.

 
Outlining Cells Referenced in a Formula

When I select a cell that contains a formula and then click in the Formula bar, any cells referenced in the formula are outlined in different colors. Is it possible to outline cells in this manner without the need to click in the Formula bar? It would be helpful to have the outlining just when a cell is selected.
—Fraser Cross (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