When you enter data into Excel you can format as you type. See how in this short video.
Related Posts
Category Archives: Videos
One Minute to Excel #25 – Find the breakeven point
Goal Seek solution
If we have a simple Profit and Loss and we want to figure out a breakeven point, we can use Goal Seek to find it.
We can also use it to see sales required to meet a certain profit.
All in less than in minute.
One Minute to Excel #24 – 1,000 random dates
A RANDARRAY solution
Let’s say we need to do some testing and we need 1,000 random dates in 2022.
We can use a new function to make this easy to create and easy to change.
RANDARRAY usually works with numbers but in Excel dates are numbers, so we get it to create random dates for us.
I set myself a challenge to do this in less than minute – see how I went in the video below.
One Minute to Excel #23 – Text numbers to real number again
Another solution
One thing you learn quickly about Excel is that there are many ways to achieve the same outcome.
This is another example. In an earlier video I showed two separate ways to convert text numbers into real numbers.
Well, I have just learned another way. An Excel MVP Rick Rothstein shared a third way. I tweaked it and share a keyboard shortcut to do it as well.
Hope you enjoy it.
Added Nov 27, 2021
If you use Text to Columns for other conversion in the same session, you may need to use Alt A E W F as the Delimiter defaults may interfere with the conversion.
Excel Alt key Shortcuts
Short video at bottom of post
The Alt key offers a way to use icons without using the mouse. In some cases, these Alt key shortcuts can be quicker than using the mouse.
One Minute to Excel #22 – Normalise a budget
Power Query can be quick
The standard budget layout isn’t great for pivot tables. You can easily and quickly convert it in to data using Power Query
See now in this short video.
One Minute to Excel #21 – Hiding Zeroes
Two ways
If you want to display a blank cell instead of a zero there are two ways to do it.
See both in this short video.
One Minute to Excel #20 – Custom Lists
Built-in feature
Do you have a list or lists that you use all the time? Would you like to write the first entry and then drag it like January to get the rest of the list? Here’s how.
One Minute to Excel #19 – Skip Blanks
Paste Special Technique
If you have data that has blanks in it you may be able to combine columns using Paste Special – Skip Blanks.
One Minute to Excel #18 – Convert real numbers to text numbers
Two techniques
When you are working with text numbers in tables sometimes you need to convert real numbers into text numbers to do look ups.
There are at least two ways to do this.
Let’s see how to convert real numbers into test numbers, real fast.
One Minute to Excel #17 – Convert text numbers to real numbers
Two techniques
When you import numbers from other systems they sometimes come in as text and are left aligned.
There are a couple of ways to fix them and they can both be done in less than a minute.
One Minute to Excel #16 – Create a useful macro
Wow - a replacement for Merge cells in less than a minute
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.
One Minute to Excel #15 – Replace Colours
It can be done
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?
One Minute to Excel #14 – Flash Fill
Excel's Super Hero
Joining names; extracting codes or converting dates is usually done with formulas, but there is now a formula-free solution called Flash Fill.
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.
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.
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.
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.
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.
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.
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.
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.
One Minute To Excel #5 – Extracting Unique Entries
Three techniques
In previous videos I worked with duplicated entries.
In this video I demonstrate how to extract unique entries from a list.
Three different ways in 60 seconds.
Off we go!
Related Posts
One Minute to Excel #3 – Filling in the blanks
No blank looks
Imported data often has missing entries you need to populate.
You can use Power Query, but that duplicates the table.
This technique works on the existing table and is quick and easy to apply once mastered.
Start the clock!
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.
Free Webinar Recording – Excel Format as Table Feature
Feedback score 95% based on 58 responses
In March 2020 I presented a session on Excel’s Format as Table Feature. I covered it’s advantages and how to use it to improve your Excel files.
The detailed pdf manual and example file can be downloaded using the button below. Content is listed below the video.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
In this session you will learn all about Excel’s formatted tables.
Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.
Formatted tables can allow you to create powerful reports like those in a relational databases.
Topics covered
- advantages and limitations of formatted tables
- keyboard shortcuts
- using formatted tables with formulas
- solutions to some of the limitations of formatted tables
- using range names with formatted tables
- using formatted tables with data validations
- creating a running total
- using PivotTables
- Relationships (Data tab)
As always I will share a few other tips.
Free Webinar Recording – Excel Budget Challenge Solution
Feedback score 92% based on 83 responses
In February 2020 I reviewed my solution to an Excel Budget Challenge from late 2018. This included some advanced Excel budget techniques.
The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
This session includes a lot of useful content and it brings together a number of different techniques. The content is worthy of a paid session but since it was a public challenge I am presenting the session for free.
The materials includes the challenge documentation, the pdf manual and the solution file. I assume you have read the documentation. You also receive the blank Excel file if you want to create your own solution.
Techniques/topics covered include
- using INDEX-MATCH (better alternative to VLOOKUP)
- 3-D formulas to summarise all report sheets and techniques to make using them easy
- using a reporting template to speed up creation
- validations
- extracting sheet names
- automating reports
Free Webinar Recording – Date and Time Calculations
Feedback score 95% based on 61 responses
In January 2020 I looked at solving some of the frustrations date and time calculations can cause in Excel.
The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
Discover the functions that handle different types of calculations.
See how to avoid some of the frustrations that dates and times can cause. Topics covered include:
- adding months to a date
- formulas for the first and last day of the month
- using today’s date in a formula
- handling days of the week calculations
- automating dates in headings
- how to avoid the traps in time calculations
- remove times from dates
- generating random dates (for testing)
As always there will be a few other tips and tricks shared during the session.