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.
In this short video I cover how to insert automated subtotals.
Its a built-in feature, that is easy to use.
When you paste into Excel from other applications, sometimes the formats can be problematic. Here is a tip to ignore formats.
Yes, you can create a cell drop down without Data Validation. It uses a built-in technique and is flexible.
Copying is a common task in Excel. This technique applies to most things in Excel form cells and range to charts, images and sheets.
It also works in Word and PowerPoint.
Have you used the mouse and keyboard together? It is time to start.
A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.
It is common to display a blank cell using the IF function and “”. A problem can arise when you want to use that IF formula in a calculation. Here is an easy way to cope.
In the previous video I removed duplicates, in this video we identify duplicates using Conditional Formatting.
I identify the duplicates twice in a minute in this video.
The clock is ticking.
I have started a new series of short videos showcasing Excel’s features that work in less than a minute.
There is a countdown to see if I can do it.
In this first one I remove duplicates in less than a minute.
The new XLOOKUP function has the ability to spill when you select multiple columns to extract. Even when you do, it doesn’t always spill across.
Recently I have been using more dynamic array formulas and have come across an unusual situation where a date is not treated like a date.
Remember dynamic arrays are currently only available in the subscription version of Excel.
Have a look at the image below.
You can download the example file at the button at the bottom of the post if you want to see it in practice.
Cell B1 is an input cell to set the first date of the sequence.
Cell E1 creates sequential numbers using the SEQUENCE function.
Row 2 has the dates and is formatted as a date.
Row 3 is the problem. If I try calculate the number of calendar days in the month using DAY and EOMONTH functions it gives a value error as if the entry in E3 is not a date.
If I multiply the E2# reference by 1 it fixes the issue as per row 4, but I shouldn’t have to do that.
I have had this is other cases where a number is not treated a number until you multiply it by 1 or perform a calculation with it.
Not sure if anyone else has seen this?
If you want to learn more about dynamic arrays I have a free Webinar Recording plus a pdf manual at the link below.
Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.
In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.
When you transform data using Power Query it will often automatically promote the headers (use first rows headers) and guess the data types for each column. You can stop it doing this.
You can use a keyboard shortcut to enter today’s date in a cell, but you can also use it in lots of other places in Excel.
I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
I was recently working with some data that had some issues with the sign on the quantities. The quantities should have had the same sign as the associated dollar amount, but they didn’t. Here is how I fixed it.
Power Query does not have an IFERROR function. It does however have another function that performs the same type of calculation.
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.
If you need to confirm a number is a whole number you can use a function with a short name.
Some people like to use bullet points in text boxes. Here is a simple technique to insert bullet points based on a list of entries in an Excel sheet.