Excel Outside the Box

Unbelieveable Excel Techniques from Excel MVP Bob Umlas

Bob Umlas

• Publisher: Holy Macro! Books
• Year: 2012
• Print ISBN: 9781615470105
• eBook ISBN: 9781615473038
• Edition: 1
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

Designed with the Excel guru in mind, this handbook introduces advanced and creative solutions, and hacks to the software's most challenging problems. Through a series of more than 50 techniques, tables, formulas, and charts, this guide details processes that may be used in any Excel application, across all disciplines. Creative approaches for building formulas within formulas, pivot tables, conditional formatting, and mastering array formulas are just some of the numerous challenges explained. Other higher level solutions discussed include using VBA macro code to override cell calculations, solve for sums from a text string, and trimming and cleaning all cells on a worksheet. This is the all-encompassing resource for advanced users of Excel wanting to learn more techniques to broaden and empower their use of Excel.

Chapter 1 - Techniques

1-A Dilemma with Relative References

Figure 1 and Figure 2 show a simple worksheet normally and with formulas showing:

Figure 1

Figure 2

Assume this goes on for hundreds of rows.

Also assume you discovered that most of the formulas are wrong – you really wanted those cells which refer to Data to be 3 rows down. That is, in cell D1 you wanted the reference to be to cell M20, not M17. How can you fix it? There’s nothing to replace – replacing 17 with 20 certainly won’t help. You can’t fix the first one and fill down because of the cells which are not of a like formula – for example, filling down would destroy the formula in cell D4. Figure 3 is what you want, how can you get there?

Figure 3

The answer (well, one answer, anyway) is to switch to R1C1 format. Yes, it actually does have a really good use! It’s done here in the File menu, Options.

Figure 4

How does that help? Look at the underlying formulas here:

Figure 5

See those [16]’s? All you need to do is change them to [19]’s, with the result shown below:

Figure 6

When you switch back to A1 notation (uncheck R1C1) you have this:

Figure 7

And you’re done! But wait – here’s an entirely different approach which also works well. First, create a new sheet, say that’s Sheet4. It will remain empty for this process, but will serve an important function as you will soon see. Change the above formulas by changing the reference from Data to Sheet4, as seen here:

Figure 8

And afterwards as you see here:

Figure 9

Now go to Sheet4, select cells M1:M3, Insert cells (Home tab), shifting down:

Figure 10

Return to the main sheet and look at the formulas:

Figure 11

Now change Sheet4 back to Data and you’re done:

Figure 12

2-Build a Formula with a Formula

Look ...

Table of contents

• Introduction
• Chapter 1 - Techniques
• Chapter 2 - Pivot Tables
• Chapter 4 - Formulas
• Chapter 5 - Array Formulas
• Chapter 6 - Charts
• Chapter 7 - VBA
• Chapter 8 - Miscellaneous