People always ask, how to do you lock Excel? In fact you have to unlock Excel and then protect the sheet. The default setting for all cells is locked, so you need to unlock input cells.
Not all recorded macros are re-usable. This print selection one is.
When you set your print area or use the Page Break Preview View, Excel will show you the page breaks on the grid. If this annoys or distracts you, here is how to remove them.
It’s a one-line macro that turns off the page breaks in the current sheet.
Sub TurnOffPageBreaks() ActiveSheet.DisplayPageBreaks = False End Sub
If you are new to macros then this blog post can take you through how to use them.
The above code can be copied and pasted into the code window.
If you want to turn page breaks off for all the sheets in the file use the code below.
Sub TurnOffAllPageBreaks() Dim ws For Each ws In Worksheets ws.DisplayPageBreaks = False Next ws End Sub
I hope these code snippets are useful.
I don’t like merged cells. Here is one more reason.
The Merge cells format has lots of issues. It can crash macros and stop you copying and pasting.
In less than a minute you can use a macro to solve the problem.
There are a few lock downs in place around Australia at the moment, so let’s look at putting Excel into lock down.
Replacing colours manually can be a tedious task.
Did you know you can use Excel’s built-in Find & Replace to do the job for you?
If you have a list of first names and last names and you want to make sure the list has no duplicates you can use a formula to confirm the names are unique.
Joining names; extracting codes or converting dates is usually done with formulas, but there is now a formula-free solution called Flash Fill.
When creating an input range you may need to validate input cells. That may mean ensuring all input cells have an entry. Here’s how.
The headings in a formatted table must be unique.
Excel has two functions to answer these questions.
If you need to find if text is in a column you can use the Text.PositionOf function to confirm it exists.
When you have a filter in place in Excel you typically only affect the visible cells when you edit multiple cells. There is a case when you are affecting all cells not just the visible ones.
The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.
A check box is an easy interface to create and use.
See how to add one to a sheet and use it in a calculation.
It is now easier to create a distinct count formula in the subscription version of Excel. You can also use a criteria. A distinct count only counts each value once. Duplicate entries are ignored.
You can’t hide a cell, but you can stop the cell value from displaying on the sheet.
It involves a custom number format.
The Australian Financial Year has its challenges. Working out the Quarter number based on a date has a few solutions. Here’s another one.
Often people perform calculations off to the right of Pivot Tables to calculate percentages.
In this short video I show you those calculations can be done inside the Pivot Table itself.
The solution is not intuitive, but it is easy.
This example builds upon the previous One Minute to Excel post.
In my previous post I created a macro from scratch that saved and closed the current file. The macro required that the file had been saved before and wasn’t read only. This post handles those two situations so you run the macro on any file and it will only work when required.
Note sure why, but Pivot Tables are often seen a “hard” or “advanced”.
In the short video we see how easy they are.
Oops – I go over my one minute time limit by a few seconds because I format the Pivot Table as well.
I started using Excel in the late 80’s on a Mac. It had a Save and Close button. When I discovered VBA in Excel on the PC, the very first macro I ever made was save and close.
I thought this would be a good example to take you through creating a macro from scratch and sharing a technique to make it easy to use.
This short video covers different ways to insert a drop down list into a cell.
I go over my one minute time limit by a couple of seconds, but I do cover three techniques.
In a financial model you often have different types of allocations that start at different times. Creating a short formula to handle this flexibility can be a challenge. Here is one solution.
Learn about Excel’s powerful Table feature that treats tables like little databases.
I ran this free live webinar in March 2021.
You can access the pdf manual and example file by clicking the button below the video.
In this short video I cover how to increment dates in long ranges.
It uses a little know dialog.
In my previous post I mentioned you should, as far as possible, keep data together in a single table rather than splitting it up between sheets. If you want to split it up for distribution purposes here is an easy way to do it.
In this short video I cover how to insert dates in long ranges.
Its simple and quick.
I have received a few questions recently relating to working with data spread across multiple sheets. In general, if the data is in the same layout, keep it in one table.