Home Newsletter

Nederlandse pagina niet beschikbaarHome > Article index > Tips & tricks

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

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!

Video showing Excel Chemistry data types

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:

Video showing how to change the color of the Excel formula error indicator

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.

FindMyCursor, a small add-in to quickly find your 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:

How to get the old-style open file dialog in Excel

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:

How to get the old-style open file dialog in Excel

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?

You can unfilter items by checking the right box

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.

Adding a comment to a table helps understanding your Excel model

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.

The accessibility checker in Excel

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!

Find previous by pressing shift while you click Find Next

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.

Closing all Excel windows at once

Dragging a range while holding shift

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

Dragging a range in Excel while holding the shift key

VBA Tips

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.

Video showing how to avoid getting a compile error each time you click outside your line of code

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!

Video showing how to enable using a hotkey to (un)comment complete blocks of VBA code

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.

Video showing how to comment a complete block of VBA 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.

Video showing drag and drop between two listboxes on a VBA userform

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!

You can save (sets of) controls in the VBA Editors toolbox

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?

You can zoom a userform easily using the Zoom property

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

Preventing auto open code from running in Excel 


Comments

All comments about this page:


Comment by: Rick Rothstein (25-2-2022 17:18:00) deeplink to this comment

Not a Shift Key tip, but related, I think. If you hold down the right mouse button while you drag the selection, you will get a menu of options to choose from when you release the mouse button at the destination location.


Comment by: Jan Karel Pieterse (25-2-2022 20:39:00) deeplink to this comment

Thanks for that tip Rick!


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].