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.
Be excellent to one another.
Bill and Ted
Movie; Bill and Ted's Excellent Adventure
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.
Name box shortcut
Just found out you can double click the re-size control on the Name Box. This quickly shrinks the Name Box width.
I have no idea how long that feature has been there, but I have just found it. Woohoo!
Related Posts
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.
Excel Power Query and Multiple Files [one hour video]
Includes detailed pdf manual
Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.
Power Query can automate the importation process. It is now a vital skill to possess.
This is a follow up session to the Introduction session which you can view here.
This session focuses on importing multiple files from a folder. The session uses CSV and Excel files plus Excel tables.
This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.
(Apologies I did have a coughing fit – I thought I had turned off the microphone but I hadn’t.)
Use the button below to download the pdf manual and example files so you can follow along.
We need to get the word out on this powerful Excel feature.
(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)
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.
Introduction To Excel Power Query [one hour video]
Includes detailed pdf manual
Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.
Power Query can automate the importation process.
It is now a vital skill to possess.
This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.
Use the button below to download the pdf manual and example files so you can follow along.
We need to get the word out on this powerful Excel feature.
(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)
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.
The Proof is in the Plants (Penguin, 2021)
A great book that brings together all the science that supports a plant-based diet.
The book is easy to read and accessible with lots of good analogies.
Plant foods are great for you and the planet and you can read about the science that backs that up.
The book pushes a mostly plant-based diet. It doesn’t push vegan or vegetarian. Being mainly plant based is the aim. The more the better.
Highly recommend the book.
I follow the Plant Proof podcast.
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.
Paste Values and Formats
If you need to paste values and formats then you can use a single keyboard shortcut after you have copied.
Alt H V E will paste both.
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.
Open the Power Query Editor
If you have cell selected in the output table from a Power Query, you can press, in sequence (not held down) the following keys Alt P U E to open the Power Query Editor window.
Related Posts
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.
Dashboard Webinar feedback
It is always nice to receive feedback – this was for the recording of the Dashboard #1 session.
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.
Financial Year in Power Query
184 is the magic number
If you need to extract the Australian Financial Year from a date in Power Query here is how to do it.
Data Validation Shortcut
I typically use the shortcut Alt A V V pressed in sequence (not held down) to open the Data Validation dialog.
I like it because you can do it one-handed. The A and V are close together.
There is another shortcut that works the same. Again, pressed in sequence and not held down. Alt D L
Use the one that is easiest for you.
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.
Pivot Table Shortcuts
Here’s a couple of useful keyboard shortcuts for Pivot Tables.
Display/Hide the Pivot Table Field List – this list lets you create or change the Pivot Table.
Alt J T L – pressed in sequence, not held down.
To add Subtotals above the entries in an existing Pivot Table.
Alt J Y T T – again pressed in sequence, not held down.
Microsoft
2021-04-08
2021-04-08
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.
Expand the Formula Bar
The Formula Bar can be expanded using the icon on the end. But there is a keyboard shortcut as well.
You can expand it or return it to one line using the keyboard shortcut Ctrl + Shift + U.
Thanks to Excel MVP Tom Urtis for sharing this shortcut recently on LinkedIn.
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.
This link above is a great resource for Power Query data connectors.
It is listed alphabetic order.