If you have a system that uses initials to identify people then being able to extract initials from a first name and last name combination can be handy. A formula can automate the process and there is also a quick, manual way to do it.
Category Archives: Excel Blog
Top 5 reports with PivotTable and PivotCharts
Switch the order of the Axis
When you create a top 5 sorted report with a PivotTable, the Pivot Chart isn’t always what you expect, there is an easy solution.
Activating a Sheet When an Excel File is Opened
Its a macro
There are times when you would like to have the same sheet visible each time a file is opened. You can achieve that with a Workbook Event macro.
Changing the Close & Load settings in a Power Query
Its a right click option
A recent attendee at a webinar posed the question, can you change the Close & Load setting on an existing query? Here is the answer.
Conditionally Selecting a Column in Excel
The IF function can return a range
When creating a formula sometimes you need to conditionally choose the column to perform a calculation on. The IF function can be used, but there is a trick to shorten the formula.
Excel Macro to Insert a Formula
When I am creating a file for my training or for my blog or other articles that I write I regularly use the FORMULATEXT function to display the formula in a cell on the right of the actual formula. To save time I created a macro to do the work for me.
Sequential Numbers Formula
A ROWS by any other name
Getting a single formula to create a series of sequential numbers is most commonly done with the ROWS function.
Extract Email from Copied Outlook Address
Macro to the rescue
I frequently copy an email address from Outlook to Excel and most times it looks like John Smith<jsmith@email.com>. To be used as an email I need to extract from between < and >. To do that in a single cell is tedious, so I wrote a macro to do it for me.
Allocating Across Financial Years
Make the most of MOD
A recent question from a CPA in Canberra allowed me to combine the MOD function and helper cells to offer a solution.
Hiding Zero Rows in Excel with No Macros
Filter technique
Over the years I have had regular requests for a technique to hide zero rows in reports. You can use macros but you can also use filters. Let’s see how you can implement a filter solution.
Linking sheet controls to cells
Easy and quick way
Most spreadsheet controls need to link to a cell to enable interaction with the spreadsheet. There is an easy way to do that.
Copying between sheets in Excel [VIDEO]
Mouse and keyboard shortcut to speed things up
Copying between sheets or files is a common task. I’d like to share a quick and easy way to do it.
Increase Font Size in VBA Window
Easy change
Yes, you can make the text in the VBA window easier to read.
It is also great for training.
(This tip may not work on 4K monitors.)
To open the VBA Window press Alt + F11.
Click the Tools menu and then Options – see image below.
Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below.
Click OK and the font size will now be increased in the code window.
See below for a comparison between 10 and 14 point.
Must easier to read!
Highest and lowest values in an Excel filtered list
AGGREGATE function solution
If you need to find the highest or lowest three entries in a filtered list you can use the AGGREGATE function to find them.
Automate Strike Through Format in Excel
Task done!
Who doesn’t get satisfaction from drawing a line through a completed task? That sense of achievement. Well you can do the same in Excel.
Unlocking coloured cells in Excel
Styles and Find solutions
When you create an Excel file that handles inputs it is best practice to colour code the input cells. The colour you choose isn’t important but making sure you use it consistently is. You may need to unlock the input cells if you plan to add sheet protection to the file. Here’s a couple of ways to do that.
Promoting Headers Issue in Power Query
Be careful if a first row name changes
Promoting headers in Power Query means using the first row as column headers. In Power Query this is a useful and common option. In some cases it is even automated. There is one time though when you don’t want to use it.
Restricting column inputs based on the current month
A data validation solution
Let’s say you have an input range that covers the whole year. You only want users to make entries in the current month column. How can you limit the month entry? The answer is a custom Data Validation.
Power Query Fill Down Doesn’t Work
The fix is easy
Sometimes when working with CSV files in Power Query you may strike the situation where Fill Down doesn’t fill down. Don’t worry there is an easy fix.
Twelve months of free Excel webinars
Free monthly Excel webinars
This month celebrates 12 consecutive free monthly Excel training webinars. One a month since August 2018.
You can view the recordings and download the materials using the table below. Sorted by Skill Level.
12 hours of free, practical CPD. Remember to keep your own records of recording viewing for CPD purposes.
Please share with the your network, enjoy!
Latest Free Excel Webinars
| Free Courses (One hour each) | Level | Recording Link | Materials |
|---|---|---|---|
| Excel Keyboard Shortcuts | Beg | Recording | Materials |
| Excel Mouse Shortcuts | Beg | Recording | Materials |
| Excel Formatting Tips | Beg | Recording | Materials |
| Excel 2016 Conditional Formats | Int | Recording | Materials |
| Excel Custom Number Formats | Int | Recording | Materials |
| Excel Financial Functions Part 1 | Int | Recording | Materials |
| Excel Financial Functions Part 2 | Int | Recording | Materials |
| Dashboard Guidelines & Techniques | Int | Recording | Materials |
| Format As Table Features | Int | Recording | Materials |
| Excel Yourself 2018 | Beg/Int/Adv | Recording | Materials |
| Mastering Excel's Form Controls | Adv | Recording | Materials |
| Excel 2016 What-If Techniques | Adv | Recording | Materials |
| Excel Budget Challenge | Adv | Recording | Materials |
| Excel Recorded Macros | Adv | Recording | Materials |
| Interactive Excel Chart | Adv | Recording | Materials |
| Introduction to Power Query | Adv | Recording | Materials |
Level Beg = Beginner, Int = Intermediate, Adv = Advanced