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

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

Video showing how to fix the column formula in a table

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

Video showing how to copy the page setup from one worksheet to another in Excel

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 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?

Video showing how to have VBA code go in debug mode when a certain considiton is true

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

Video showing how prevent deleting a worksheet using VBA

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!


Comment by: Aditya (3-5-2023 04:39:00) deeplink to this comment

Please make more articles on VBA I am from Civil engineering background so please make some artical about VBA automation.


Comment by: Jan Karel Pieterse (3-5-2023 08:59:00) deeplink to this comment

Hi Aditya,

Do you have any specific requirements?


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].