Excel tips & tricks
Content
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
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!
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?
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:
Option Explicit
Private Sub Workbook_SheetDeactivate(ByVal
Sh As Object)
ThisWorkbook.Protect , True
Application.OnTime Now, "ThisWorkbook.RemoveProtection"
End Sub
Sub RemoveProtection()
ThisWorkbook.Unprotect
End Sub
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.
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!
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.
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.
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!
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
