Home > Templates > Templates > Alex Blakenburg

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

ET0002 - Insert a Degree Symbol

If you're typing numbers that are temperatures, there's no built-in number format that applies a degree symbol. Instead, you can use a custom number format, to automatically add a degree symbol after each number.

Follow these steps if your computer keyboard has a number keypad, at the right side of the keyboard.

1. Select the cells that should have degree symbols
2. Press Ctrl+1, to open the Format Cells window
3. In the Category list, click Custom
4. In the Type box, enter 0.0
5. Next, press the Alt key, and on the number keypad, type: 0176
• If you don't have a number keypad, see the note below the screen shot, for an alternate way to enter a degree symbol
6. Optional: Type a C or F, to show the measurement system
7. Click OK, to apply the custom number format

Enter Degree Symbol Without Number Keypad

If your computer doesn't have a number keypad, try this alternate method to enter a degree symbol.

• On the keyboard, press the Windows key, and then tap the period key
• When the Emoji window opens, click the Symbol icon at top
• At the bottom, click the General Punction icon
• Scroll down about half way, to find the degree symbols
• Click the plain degree symbol, or the symbol with C or F

Then, return to Insert Degree Symbol steps above, from Step 6, to complete the custom number format.

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

You can find more of Alex's Excel tips and solutions on the Mr. Excel forum, where he has been awarded as an MVP.

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