Joining names; extracting codes or converting dates is usually done with formulas, but there is now a formula-free solution called Flash Fill.
See how you can do four transformations in less than a minute.
Category Archives: Excel Blog
Make Sure All Input Cells Have an Entry in Excel
COUNTIF to the rescue
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.
Excel Formatted Table Headings
No duplicates allowed
The headings in a formatted table must be unique.
How Many Rows and Columns in a Range
Two functions to the recue
Excel has two functions to answer these questions.
Power Query Finding Text in Another Column
Return TRUE if text is in another column
If you need to find if text is in a column you can use the Text.PositionOf function to confirm it exists.
Filter Issue with Excel
Be careful with formatted tables
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.
Missing Chart Data in Excel
Another macro to the rescue
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.
One Minute to Excel #13 – Create and Use a Check Box
Tick it to the next level
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.
Distinct Count Formula in Excel
New and old functions combined
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.
One Minute to Excel #12 – Hide Cell Entries
It is a special format
You can’t hide a cell, but you can stop the cell value from displaying on the sheet.
It involves a custom number format.
Australian Financial Year Quarter Formula
Another CHOOSE solution
The Australian Financial Year has its challenges. Working out the Quarter number based on a date has a few solutions. Here’s another one.
One Minute to Excel #11 – Add % to a Pivot Table
It is not intuitive
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.
Save and Close extra
Handling read only and new workbooks
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.
One Minute to Excel #10 – Create a Pivot Table
It is easy
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.
Save and Close in Excel
Let's create a macro
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.
One Minute to Excel #9 – Drop down list in a cell
Three techniques
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.
Financial Model Allocation Technique
Helper cells to the rescue
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.
Excel’s Formatted Tables [Video]
Free webinar recording
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.
One Minute to Excel #8 – Incrementing a Long List of Dates
It is easy when you know how
In this short video I cover how to increment dates in long ranges.
It uses a little know dialog.
Segmenting a Table with Power Query
Slicing and dicing a table
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.
One Minute to Excel #7 – Copy Date down a Long Range
Quick and easy technique
In this short video I cover how to insert dates in long ranges.
Its simple and quick.
Don’t split your data in Excel
If you have Power Query you can consolidate the data
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.
One Minute to Excel #6 – Add Subtotals Automatically
Built-in feature
In this short video I cover how to insert automated subtotals.
Its a built-in feature, that is easy to use.
Control Pasting from Other Apps
Special paste option
When you paste into Excel from other applications, sometimes the formats can be problematic. Here is a tip to ignore formats.
Excel Drop Down without Data Validation
Alt key solution
Yes, you can create a cell drop down without Data Validation. It uses a built-in technique and is flexible.
One Minute to Excel #4 – Copy Anything
Mouse and keyboard combined
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.
Let’s go.
How to Refer to a Column in a Spill Range
INDEX rules
A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.
Handling Formula-Based Blanks
The N function to the rescue
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.
One Minute to Excel #2 – Identify Duplicates
Short, sharp video tips
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.
One Minute to Excel #1 – Remove Duplicates
Working against the clock
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.