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.
Category Archives: Excel Blog
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.
Moving Images in Excel
Make the Shift
When you are moving images, charts or other objects that float above the grid you can use the Shift key to make it easier.
Entering Dates in Excel
Stop the full stop
There are only two characters Excel recognises when separating numeric days, months and years in dates. They are the / and – characters.
Please don’t use the full stop as Excel won’t recognise it as a date.
Below you can see examples of using / and – in dates.
When you use the full stop Excel won’t recognise it as a date – see below.
Its left aligned and will be treated a text.
Pasting or inserting with a Formatted Table
When a little icon makes all the difference
When you need to paste new rows to the bottom of a formatted table, or insert blank rows at the bottom, one way is much quicker than others when working with large, complex models.
Autofit columns with a limit
A macro to the rescue
The column Autofit on the whole sheet is a great Excel feature. But if you have a few columns that have lots of text it can make using it problematic as you need to manually adjust those wide columns. Here’s a macro to make it easier.
Increasing Excel Formula Bar Font Size
Good for training
I recently saw a good post on Linkedin from Leila Gharani MVP on increasing the Excel Formula Bar font size. It seems useful for training, so I wrote a simple macro to simplify the process.
Handling plurals with the IF function
Singular or plural?
Sometimes when creating text you need to handle plurals correctly. The IF function makes it easy.
Unique Entries in Excel via a Conditional Format
Filtering to the rescue
Excel’s Conditional Formatting feature has a built-in unique option. Its unique option only identifies entries that are not repeated. This is different to the Advanced Filter Unique option which lists each unique item from a range once. To filter by entries only appearing once you can use Conditional Formatting with filtering. No formulas required.
PivotTable Grouping
Create your own categories
Grouping of dates in Excel’s PivotTables is fairly common and in the most recent versions of Excel, automatic. Many people don’t realise that you can perform other types of grouping in Excel.
Allocate based on start date and number of months
EDATE to the rescue
Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard.
Improved Data Structure
Tables rule
I ran a webinar in January 2019 where I presented and explained a budget challenge file I had submitted in November 2018. I mentioned during the session that I didn’t like the layout of the Data tab. Well someone asked how should it look? So here is how I would have arranged it.