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.
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.
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.
When you are moving images, charts or other objects that float above the grid you can use the Shift key to make it easier.
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.
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.
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.
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.
Sometimes when creating text you need to handle plurals correctly. The IF function makes it easy.
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.
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.
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.
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.
The Data Validation dialog allows you to limit or control what a user enters into a cell. There are two keyboard shortcuts to open it.
Let’s say you have a weekly roster. You have a date and you need to identify the Monday start date for the week that contains that date. The formula is fairly easy.
Sometimes when writing code you use the same text string in multiple places in your code. To make things easier you should capture that text string in a variable.
There is a complicated way to extract the entries from a slicer but there is also an easy way to do it.
I recently received an unusual request about sorting. They wanted to sort in ascending order but they wanted to ignore the sign of the values. So -44 would be next to 44.
Excel has a few counting functions. But when it comes to counting entries in a cell it can be difficult if you are using formulas that return a blank cell. This is where the SUMPRODUCT function can come to the rescue.
Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count.