If you need to find the highest or lowest three entries in a filtered list you can use the AGGREGATE function to find them.
Category Archives: Excel Blog
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!
12 Months of 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 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 |
Level Beg = Beginner, Int = Intermediate, Adv = Advanced
Handling Exceptions in Excel
Two ways to consider
When developing budgets and financial models you may need the same rate/value (say debtor days) for 10 months of the year but need to adjust it for 2 months. It is usually December and January as they tend to be a little different due to the Christmas / New Year period. Here’s a couple of ways to handle exceptions in Excel.
Excel ISDATE Function
A few solutions
Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.
Autofit Rows with a Maximum Row Height
Follow up post
In March I wrote a post on using a macro to apply column Autofit on a sheet but with a maximum column width. This is a follow up post as someone requested the same functionality for row height.
Default Function Arguments in Excel
What to leave out
Many of Excel’s functions have default arguments. What are arguments? Arguments are the sections within a function that you separate with commas and some of these arguments are optional.
Zeroing an input range in multiple cells
Range names make it easy
In some cases you may have to make manual inputs across multiple cells that are spread across a sheet. Before making the entries you need to clear the existing entries. Creating a range name can make that process much quicker.
Selecting a single word in Excel
Mouse shortcut
If you are editing a sentence or a heading and you need to replace a single word there is a quick and easy way to do it.
Excel Comments (Notes) and Freeze Panes
Row height solution
Inserting a Comment (renamed to Notes in newer versions of Excel) in the first row and then using Freeze Panes to keep that first row visible can cause issues displaying the Comment (Note). Here’s how to fix it.
Multiple Entries in an Excel Filtered List
Ctrl key to the rescue
When a list is filtered you are only seeing the rows that match the filter. The other rows are still there, just hidden. If you want to make the same entry in a group of filtered cells you can’t use the fill handle to drag and copy as you will overwrite the hidden rows. There is an easy way to do it.
Switching Rows and Columns in a PivotChart
Finding the hidden option
Switching between rows and columns in a normal chart is easy via a button on the Design tab. But how do you do it with a PivotChart?
Excel has Icons
New feature
If you have the subscription version of Excel, check out the Insert tab – you have Icons!
Selecting the same cell in all the sheets
It comes with a warning
Let’s say you want the active cell to be A1 in all the sheets in a file before you save the file. There is an easy and quick way to do it. It doesn’t matter how many sheets are involved, but it does come with a warning.
Automate Input Cell Colour
Use a CELL function option
It is best practice in Excel to have a consistent colour for input cells so that users know where they can and need to make changes. You can automate this process by using a Conditional Format.
Excel, Subtraction and Accuracy
Binary takes a byte
It’s not mentioned a lot, but some of Excel’s subtraction calculations are not exact. When you look at them at 15 decimal points they are slightly out. Most times this make no measurable difference to the result. But sometimes when you round, it does.