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.
Following on from my last two VBA posts here is how you can identify the cell addresses of the first and last cells in a range.
In my last blog post I found the last used row and column numbers on the active sheet. This post lets us find the row and column extremities of a specific range.
When writing VBA code it is common to need to know the last used row and/or the last used column. These values provide the edges of the sheet contents.
Here is a technique that allows you to turn off and turn on the conditional format without actually removing the conditional format. You may want to do this to print a sheet without the conditional formats being applied.
Do you use the Ctrl key and the mouse to select multiple ranges? Well you may want to watch this short video.
When creating macros that work with filters it is a good idea to remove filters at the beginning of the macro code. Here is how you do that.
Ever seen a sheet with what looks like missing gridlines? Wondered what happened? The issue is a format.
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Let’s assume you need to fill a vertical range with all the whole numbers from 1 to 100. There are lots of ways to do this, but I think the keyboard could be the quickest.
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart.
Would you like to change the format of all your formula cells so they have a different fill colour or font? There is a way in Excel 2013 onwards.
In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.