Contextures

Alex Blakenburg Excel Tips

Microsoft Excel tips and examples from Excel expert, Alex Blakenburg, for free download. Excel examples to reverse names, fill blank cells, and more.

VBA

VB0002 - Sheet Name Macros

Use Alex's macros to get the sheet name for a specific Excel table. Alex needed code to make changes to a named Excel table, and to its worksheet, and there were a few things to consider:

  • Macro would be run from a different workbook, not the file with the table
  • Table's workbook might not be the active workbook - other workbooks were being created and edited
  • Someone might have changed the sheet name (it's unlikely that the table name would be changed)

So, these macros have the table name hard coded (OrderRef), but not the sheet name.

VB0001 - Fill Blank Cells

Use Alex's macro to fill blank cells in a column, using the values from above. Alex's code starts from the active cell, and stops at the last row in the active column. If there are text numbers, the code doesn't change them to real numbers, as other macros do.

Or, watch this video to see how to fill those blank cells manually.

Functions

FN0002 - LOOKUP Function Exact Match

Although the LOOKUP function doesn't have an Exact Match option, Alex shows how to build a formula that finds an exact match, if one exists.

This short video shows other LOOKUP formula examples.

FN0001 - Reverse Names

After seeing my long formula for reversing first and last names in a cell, Alex suggested this improved short formula:

=MID(A2&" "&A2, FIND(",",A2)+2, LEN(A2)-1)

This video shows the steps for building that formual, and there are written steps on the How to Split Names page

Excel Tips

ET0001 - Close All Open Files

In Excel 2013 and later versions, each file opens in a separate window. Unlike earlier single-window versions, there is no Exit button or command, to close all the files, without clicking each window individually.

Alex shared the following shortcuts that you can use.

  • If multiple workbooks are open, any of these shortcuts will close the active Excel workbook only
    • Alt+F4
    • Ctrl+F4
    • Ctrl+W
  • If a single Excel workbook is open:
    • Alt+F4 shortcut closes the active Excel window, and also closes Excel.
    • Ctrl+F4 and Ctrl+W shortcuts close the active Excel window, and leave Excel open

Another option is to add Close buttons to the Quick Access Toolbar - see the steps on the Excel Files FAQ page. This video shows the steps for adding buttons to the Quick Access Toolbar (QAT).

About Alex Blakenburg

Alex Blakenburg is in Sydney, Australia.  He has spent most of his working life as a Systems Accountant and is passionate about extracting data out of ERP & Payroll systems and converting that data into useable information. Linkedin Profile: Alex Blakenburg

NOTE: These Excel tips and examples are provided "as is" for the purpose of illustrating Excel techniques. It is expected that readers will use these examples to develop their own solutions. There is no support provided for these solutions, and no warranty of usability is provided or implied.

More Excel Files

Excel Sample Files

Ron Coderre

Roger Govier

Get weekly Excel tips from Debra

 

Last updated: July 11, 2021 10:10 AM
Contextures RSS Feed