Cool Excel Sh*t

Bob Umlas

• Publisher: Holy Macro! Books
• Year: 2021
• eBook ISBN: 9781615471591
• Edition: 1
• Topic: Computer Science
• Subtopic: Business Skills
• Index: Computer Science

Cool Excel Sh*t is designed with the Excel guru in mind, introducing advanced, creative solutions and hacks for the software's most challenging problems. Through a series of more than 50 techniques, formulas, dynamic arrays, and VBA macros, this guide details processes that may be used in any application and across all disciplines. Includes a section on techniques using Dynamic Arrays in Excel.

MISCELLANEOUS

Returning from clicking on a hyperlink

Once you click a hyperlink, you can return to the source by pressing Alt/Left arrow!

You can type R1C1 notation into the Name box:

and when I hit enter:

I guess column ALL is column 1000!

Play a trick on your coworkers by hiding even row numbers

What happened to the even row numbers?

How to do this is select a cell in row 2 and give it a font of 120-pt size. Say that was B2. Select B1:B2 and use the fill handle to drag down 50 rows or so.

It’ll look like this:

Now select everything (click on the triangle above row 1 and to the left of column A), and change the row height to 20 or so. That’s all there is to it!

Another one: Go to your coworker’s computer, select all cells and format them as ;;;

Now, whatever is typed will disappear!

Print Area manipulation

To clear the Print Area you can either set the Print Area when all cells are selected, or choose the Clear Print Area command.

Did you know that if you already have a print area and want to add another range to it, another choice shows up?:

Here. C9:F14 is the print area. If you select A1:C5, you can see a new command:

Change Rows to Repeat at Top On All Worksheets

Even if you open the Page Setup dialog while in group mode, you can not change the Rows to Repeat at Top on multiple worksheets at one time.

This macro will set the rows to repeat to rows 1 & 2 for all sheets in the active workbook:

Sub ChangeRowsToRepeatAtTop()

Dim WS As Worksheet

For Each WS In ActiveWorkbook.Worksheets

WS.PageSetup.PrintTitleRows = "$1:$2"

Next WS

End Sub

To change Columns to Repeat at Left, use .PrintTitleColumns = “$A:$B”.

A few miscellaneous tips & tricks

• If you often enter large numbers, like a million or a bil...

Table of contents

• Foreword
• FORMULAS
• FORMATTING
• MISCELLANEOUS
• DYNAMIC ARRAYS
• VBA
• Index