Excel tips & tricks

Content

• Introduction
• General Excel tips
  • Fixing the formula in a column of a table (NEW)
  • Copy page setup to other worksheet
  • Fun with Excel data types
  • Formula error indicator
  • Find my cursor
  • Fill justify nicely justifies text
  • Open file dialog
  • Filtering to leave items out
  • Why adding a comment to your table is important
  • Accessibility ribbon tab
  • Clicking Find next Using Shift finds previous
  • Quickly close all open workbooks
  • Dragging a range while holding shift
• VBA tips
  • VBA Watch expression break on True
  • Prevent deleting a sheet without protecting the workbook
  • VBA Editor syntax check setting
  • VBA Editor commenting a block of code 1
  • VBA Editor commenting a block of code 2
  • Drag and drop in a userform
  • The VBA Editor's control toolbox can do more than you think
  • Userform Zoom property
  • Long running macro

Introduction

I regularly post tips and tricks on my linked In page. I'm collecting those tips and tricks here for your convenience.

Move your mouse over the pictures or tap on them, to animate them!

General Excel tips

Fixing the formula in a column of a table

Sometimes when you are working in a table and add a new row, Excel complains that the formula in the newly added row deviates from the other rows in the table. Excel keeps a copy of what it calls the column formula stashed away somewhere. You cannot directly edit that hidden column formula, but you can easily update it when you know how.

Here are the steps to fix the column formula:

• Select all cells in the column
• If the formula in the formula bar is correct, press F2 or click in the formula bar
• Hit Control+Enter

Copy page setup to another worksheet

This trick makes it easy to duplicate an intricate page setup from one worksheet to another. All it takes is:

• Select the worksheet with the page setup you want copied
• Control+click on the tab(s) you want to have the sme page setup
• Open the Page Setup dialog by clicking the dialog launcher (watch the video to see where that is)
• Click OK

Fun with Excel data types

In my first job I was a Chemical Engineer. So this makes my heart beat a bit faster! Excel data types are awesome!

Formula error indicator

Excel shows a little green triangle in cells when it finds something might be wrong with your formula. I think green isn't the right color for something that might be wrong. Luckily you can change that indicator color, so I change it to orange on every new Office installation I use:

Find my cursor

I often work on workbooks contaiing a lot if information on a worksheet/ Sometimes the worksheet design and zoom make it difficult to see which cell is selected. I've created a small add-in which animates the selection called FindMyCursor to make it easier to find the active cell.

Fill justify nicely justifies text

I know, Excel isn't a Word processing application. But sometimes you do need some text to nicely flow across a number of rows without multiple lines in one cell. Here's how you do that:

Wim Gielis pointed out that there is a 253 character limit. I've tested this and indeed, sentences with more than 253 characters in a single cell get truncated during Justify.

Open file dialog

I don't like the newfangled File, Open "experience" Microsoft designed for us, I prefer the old-school "explorer-like" dialog. As it happens, you can get that old dialog to browse for your files. Just open File, Options and uncheck the appropriate box on the Save tab:

Filtering to leave items out

The autofilter drop-downs are very powerful. Did you know you can use the checkboxes to remove items from an existing set of filters?

Why adding a comment to your table is important

Did you know you can add a comment to your table? You have to do so using the Name Manager (Formulas tab). The comment then appears when you type a table name in a cell.

Accessibility ribbon tab

On average, spreadsheets are used by 13 (!) people. This means there is a significant chance that your spreadsheet might need to be used by someone with disabilities. The accessibility tab helps you to find potential accessibility issues in your file.

Clicking Find next Using Shift finds previous

Did you know you can reverse the action of some buttons? The Find Next button on the Find dialog is one of them, hold shift while you click Next to find the previous entry!

Quickly close all open workbooks

Sometimes you just have too many workbooks open in #Excel... Closing all of them is tedious, or is it? You should hold shift ONLY when you click that close box. Then release shift. Then you get to decide whether or not to save each workbook that has been modified one by one. You can press shift when you click No on the "do you want to save dialog", but ONLY do that if you are absolutely 100% positive then none of the workbooks need to be saved. In the video, I held down shift when I clicked that No button.

Dragging a range while holding shift

The shift key also affects the drag behavior of the mouse:

VBA Tips

VBA Watch break on True

Even though I am an experienced VBA developer, my code does generate a runtime error some days. Stepping through code to debug it is time consuming, especially on a loop that runs many cycles. Or is it really?

Prevent deleting of worksheets without protecting the workbook

This tip (like many others) stems from a forum question I answered many years ago. The questioner wanted a way to prevent the users from deleting worksheets, but he did want to allow them to insert worksheets and rename them. He therefore did not want to protect the workbook. The code below allows you to do that.

It works by placing this code in the ThisWorkbook module of your file:

VBA Editor syntax check setting

In the Office VBA editor, there is one default setting that annoys me tremendously. The checkbox I'm referring to triggers a compilation every time you have changed a line of code and move to a different line. Causing a compile error if anything's wrong.

VBA Editor commenting a block of code 1

If you write VBA code a lot, you may also want to comment multiple lines ov VBA code in one go. Don't type all those ' characters line-by-line, be smart!

VBA Editor commenting a block of code 2

To make commenting blocks of code even simpler, adjust the toolbar button to comment code so it shows its caption, then you can use alt+c to comment a block of code.

Drag and drop in a userform

Some time ago I wrote an article about implementing drag and drop between (and inside) listboxes on a VBA userform. If the listbox is not big enough to display all items, the listbox will scroll during the drag, to ensure hidden items scroll into view so you know where you are dropping. See this article.

The VBA Editor's control toolbox; smarter than you think

Did you know you can add your own tabs and (sets of) controls to the VBE's control toolbox? Simply drag the selected controls to the toolbox to save them for later use!

Userform Zoom property

I write a lot of VBA code in Excel. One of the challenges when creating a user interface is the different screen resolutions and sizes. Did you know you can change the zoom percentage of a UserForm to quickly make it appear larger (or smaller) on screen?

Long running macro

You have an Excel workbook which contains a macro that automatically starts. What if you want to edit the workbook but don't want to wait for that long macro? See this article about preventing the workbook_Open event from running