Excel Guidance Videos & Articles

Starting at £0/mo
Click PREVIEW below to view our library of free Excel learning resources comprising 100s of videos, download files and other resources.  WANT MORE? Check out our Membership plans (below) for unique communities, learning resources and exclusive sessions with Chris. Select our FREE blog membership to get email notifications of new articles and videos.

The Only 3 IF Statements You Need In Excel VBA - Excel VBA Beginner Fundamentals (6/6)

The Only 3 IF Statements You Need In Excel VBA - Excel VBA Beginner Fundamentals (6/6)


Conditional statements allow us to ‘send’ Excel in one of two (or more) directions when running VBA code.  They work through a ‘condition’ or a logical statement – is something happening in the workbook?  
 

CLICK HERE TO WATCH THE VIDEO

For example, is the value in cell A1 more than 10?  If the ‘condition’ or logical statement is met, then Excel jumps to one place in the code; if not, it jumps to another.  In this way, Excel diverts the code in one of two directions.  Cool!
 
Conditional statements feature in most powerful computer programmes.  I love learning (and teaching) conditional statements because you only need 3 structures to do everything you would need to do involving conditionality in Excel VBA.  They build nicely from a very simple to sophisticated.  So, let’s look at the first one.
 
 
1. ONE-LINE CONDITIONAL STATEMENT USING IF
The first building block is a one-line conditional statement using IF.  It’s simple but, applied correctly, is often enough to get the job done.  In literal terms, it tells Excel to do something if something is happening.  There are a few syntax rules to observe here – the most important of which is the whole statement must be on a single line.  That means both the condition - and what to do if the condition is met.
 
Follow along with the video and build and test the one-line conditional statement with me.
 
2. IF – ELSE – END IF
The second building block for conditional statements in Excel VBA is more powerful but significantly more difficult to put together.  IF – ELSE – END IF gives us two options: what to do if the condition is met, and what to do if it isn’t.  
 
Once again – syntax is important.  The main difference compared to the one-line conditional statement is the instructions are now placed on a different line to the condition.  This can confuse and has cost me hours of time over the years!  Once you’ve practiced the basic IF – ELSE – END IF structure, however, you’ll be harnessing its power for years to come.  Follow along with the video as I build, test and debug it.
 
A sidenote here: you can build on this structure using ELSE IF to introduced more than one mutually exclusive option.  I find this syntax too complicated, however, which is why I favour our final building block …
 
3. SELECT CASE
So, we’ve looked at options to divert Excel in one direction if a condition is met, and in one of two directions.  But, what if you needed to programme 3 options, or more?
 
This is where SELECT CASE comes in.  Yes, the language is different which certainly takes some getting used to.  But, it’s so worth it in the end because SELECT CASE gives us the flexibility to programme any kind of conditionality logic using Excel VBA.  It’s one of my all-time favourites!
First, we use SELECT CASE (object) to show Excel what to look at.  Then, we list options for the (object); Excel automatically jumps to the option that applies.  We can even introduce a CASE ELSE catch-all option to ensure all bases are covered.
 
Once again, follow along with the video as I create a SELECT CASE statement with Excel VBA.  If the syntax is too much for the moment, that’s fine: simply watch the video and try to appreciate the power of SELECT CASE – you’ll be using it in no time!
 
So there we have it, the three structures I use to programme conditionality with Excel VBA.  If you’re steady and systematic and build from one idea to the next, I’m absolutely sure you’ll be able to integrate these powerful mechanisms into your Excel VBA vocabulary, adding flexibility and power to your VBA applications.
 
If you’ve been following along, I hope you’ve enjoyed the Excel VBA Building Blocks series.  Finally, there will be a special session to celebrate the end of this series only with those who have signed up for the resources on the website.  So, make sure you sign up at the link above to access this special live session, the details of which I will send out in the coming weeks.
 
ABOUT THE EXCEL VBA BUILDING BLOCKS COURSE
 
 
Learn the fundamentals of Excel VBA in around an hour with Tiger's FREE Excel VBA Building Blocks course. The course is based on Chris' 10 years of Excel training, consultancy and content creation, and teaches the basics of Excel VBA around 18 simple ideas or 'building blocks'. 
 
In addition to the 6 basic videos on Excel VBA, on the website you can find more  'metaskill' videos (to be uploaded through Summer 2021) about next level concepts that could unlock your potential with Excel VBA. 
 
A course handbook is provided for free that lists each of the Excel VBA beginner building blocks and includes a learning template for each session - make sure you download and use it to support your learning. 
Use the link below to download the course handbook and access the learning template for this session.

Beginner Survival Guide To Working With Dates And Times In Excel

Beginner Survival Guide To Working With Dates And Times In Excel
For me, working with dates and times is the most frustrating thing about Excel.  No exaggeration – it’s genuinely the most frustrating thing.


CLICK HERE TO WATCH THE VIDEO
 
I’ve wasted hours and days trying to resolve problems with times and dates in Excel.  And, I have to admit, I’ve still not mastered it.  But, I have learned a lot.  In this video, I share what I know about times and dates in Excel, from the concept of how Excel understands dates and times, to helpful date and time formulae, and typical situations you might encounter - and how to deal with them.  So, let’s get started with times and dates in Excel …
 
 
00:00 INTRODUCTION  
00:35 CONCEPT: HOW EXCEL TREATS DATES
 
The first thing to understand is that Excel treats dates as numbers.  Yes, they appear in date ‘format’ (eg. MM/DD/YY), but the underlying data is a number – it’s just the formatting that makes it appear different.
To test this, type ‘1’ into a cell, then convert the cell formatting to ‘Date’, as Chris does in the video.  The result should be ‘01/01/1900’.  What’s the significance of this date?  
 
Well, it’s what Excel understands as the ‘beginning of time’.  This, for Excel, is date no.1 and it’s represented by ‘1’.
 
So, if Excel actually understands dates as numbers, what’s today’s ‘day number’?  To understand this, let’s reverse the process and discover a few new formulae as we go.  Input the =TODAY() formula into a cell as Chris does in the video.  Today’s date should automatically appear – note how Excel autoformats the cell to display a date.  But, we know the underlying value is simply a number – so what is it?  To find out, clear the formats from the cell.  This reveals today’s ‘day number’ and reinforces the idea that Excel actually understands dates as numbers.  Got it? Good!
 
02:45 CONCEPT: HOW EXCEL TREATS TIMES
 
In addition to the TODAY formula that tells us today’s date, the =NOW() formula is also useful and teaches us how Excel understands times.  Type =NOW() in the cell below and let’s compare the two.  Once the formats are removed from the cell, you can clearly see a decimal.  What’s that all about?  Well, we’re familiar with the day number from the previous formula, so what does the decimal portion of this number represent?
 
 
To find out, make Excel calculate as Chris does in the video, and note how the decimal ticks over.  Yes, this decimal actually represents time!  That’s because Excel understands time as a fraction of a whole number.  It kind of makes sense since we could think of time as a portion of the day that has gone – at least this is how Excel thinks about it!  Reinforce this idea by typing some decimals into the spreadsheet and formatting them as time.
 
04:18 14 USEFUL FORMULAE FOR DATES / TIMES IN EXCEL
 
As we have seen, how Excel (auto-) formats your data is important.  Rather than playing with cell formatting, however, I recommend using Excel’s date formulae to extract the ‘component’ of the time / date that you need.  Want a cell to display the month of a date, for example?  Use the =MONTH() formula.  There are others too, right down to the second!  And don’t forget the =WEEKDAY() formula that translates the day of the week into a number, starting from 1 for Sunday.
 
Don’t forget the basic idea – that Excel understands dates as whole numbers and times as decimals: this should begin to make things easier.  This means we can use basic arithmetic formulae, for example, to find the difference between two dates, as Chris demonstrates in the video.
 
07:06 FIND NO. DAYS BETWEEN TWO DATES
 
Oh, you want to know more about the difference between two dates in Excel?  Yes, the basic subtraction method works, but there are some other cool formulae you should know.  =DAYS() does the same thing in a tidier manner.  The =EOMONTH() and =EDATE() formulae are even better: the first gives the date of the last day of the month, while the second returns the ‘equivalent’ date in a different month.  Yes, because not all months have 31 days, these formulae are essential for navigating the calendar.  =NETWORKDAYS(), meanwhile, finds the difference between two dates counting workdays only.  Cool!
 
10:40 3 COMMON PROBLEMS AND HOW TO DEAL WITH THEM
 
Now you know the basics, what common problems might you encounter when working with dates and times in Excel, and how would you deal with them?  In the video, I share 3 fixes I use all the time in my Excel development work.
 
So, there you have it, my survival guide for working with times and dates in Excel.  Any nightmare stories to share about working with times / dates in Excel?  I’ll lend an ear in the YouTube comments and I hope these tips help you get to grips with this tricky topic.
 

Create An Excel Combo Chart In 7 Easy Steps (MM LITE #3)

Create An Excel Combo Chart In 7 Easy Steps (MM LITE #3)

 
A combo chart is one of the coolest things in Excel: a visual that combines two charts together into one.  Putting one together, however, is tricky.  The following tips apply equally if you’ve never created a chart in Excel, or if you’re looking to up your Excel chart game.  So, let’s see how to create an Excel chart in 7 easy steps.
 
CLICK HERE TO WATCH THE VIDEO

00:00 INTRODUCTION
 
00:28 PREPARE DATA
Yes, charts in Excel can be a nightmare!  But, the root of the problem is not always the chart itself because people often try to chart ‘unchartable’ data.  You see, you’re only ready to create a chart when the target data is neatly analysed and tabulated.  Yes, a tabulated analysis is essential for all Excel charts including a combo chart.
 
🔥FREE NON-PUBLIC ONE-HOUR EXCEL VBA TUTORIAL
 
How to create a tabulated analysis?  Using formulae such as COUNTIF (for discrete) and FREQUENCY (for continuous data), it’s probably easier than you think.  Check out our video called ‘Can’t Do Data Analysis In Excel?  Just Do This!’ for a step-by-step guide.  You’ll be creating powerful data analyses in no time …
 
00:50 PRE-SELECT DATA
Next, my number 1 tip for chart creation in Excel: pre-select your data.  A empty chart can be demoralizing – but that’s exactly what Excel creates if you don’t pre-select your data.  So, select the data first then go to ‘Insert’ and click your chart of choice.  What, you don’t know what type of chart to create?  More on that later …
 
I recommend at this point selecting a ‘block’ of data, even if it does not display exactly what you want – we can also adjust the series later, speaking of which …
 
01:16 ADJUST SERIES RANGE FOR ‘PRIMARY’ SERIES
Whether this step is necessary or not depends on how well your data is set up.  If the column that contains axis labels and the column that contains data you wish to plot (ie. the ‘series’) are not adjacent, you’ll have to adjust the series for the chart.  This is easily done using the visual cues Excel gives us – see how I make the adjustment quickly in the video.
 
03:00 ADD AXIS TITLES – PRIMARY AXIS
Where the meaning of an axis is not clear, axis titles are essential to help the user understand what’s going on.  They are particularly important for combo charts because the whole point is that we chart or ‘combine’ two sets of data on the same chart.  Not only should we title the axes, therefore, we must also make sure the user understands which axis corresponds with which series on the Y (vertical) axis – more on that later!
Note that, in this case, the meaning of the X (horizontal) axis is clear enough since month names are used, so there’s probably no need to add another axis title which might create unnecessary clutter.
 
 
04:10 ADD SECOND SERIES
Now – let’s turn a normal line chart into a combo chart!  The first step is to add that second dataset or ‘series’.  In our case, we wish to show monthly profit or loss (secondary) alongside the cumulative profit or loss (primary).  Once again, the fact the data is well-prepared makes the job easy through the ‘Select Data’ interface.  What we should have now is a chart with two series – not quite a ‘combo’ chart, but certainly close …
 
05:10 CHANGE SECOND SERIES TYPE
The defining feature of a combo chart, for me, is the visual effect of two different types of chart combined together.  Follow along with me in the video as I change the second series to a ‘column’ chart.  And there we have it – the organized chaos of a combo chart!  But, a chart that’s too chaotic is simply not helpful, so we should do everything we can to make the chart easy to interpret …
 
06:20 ADD AXIS TITLE – SECONDARY AXIS
Yes, Excel allows us to add an additional axis to a chart which means one axis on the left, and one on the right!  This can confuse – so let’s make it as easy as possible for the user by titling the axes properly.  Even better, format the axis titles and so that the colours are the same as the series they represent, to subtly reinforce the point …
 
07:00 BONUS DISCUSSION: WHAT TYPE OF CHART IS BEST?
The above guidance is somewhat academic if you’re not using the right type of chart.  So, briefly, what type of chart should you use in what situation?  I talk about three main types of charts: bar or column, pie and line and share further guidance in the video.

 

My No1 Excel VBA Technique Explained In 10 Minutes

My No1 Excel VBA Technique Explained In 10 Minutes

 
Yes!  I’ve been looking forward to this one – a beginner Excel VBA tutorial on my number one Excel VBA technique.  Welcome back to the Excel VBA Beginner Building Blocks series, you can download the series resources and access secret videos using the link in the video description.
 
 
 
CLICK HERE TO WATCH THE VIDEO
So, what about this ‘number one’ VBA technique?  It’s my number one technique firstly because, on its own, it’s pretty cool.  But there’s more than that: it binds together other powerful VBA techniques (variables, loops etc.) to create beauty in Excel.  This is why, for me, this technique is responsible for more ‘punch-the-air’ moments than any other, over the years.  Let’s create one for you today!
 
 
To understand the power of the technique, we first have to appreciate the concept of ‘position’ and its importance in Excel.  Like in chess, command of a grid in Excel gives us ‘control’.  VBA’s Offset method makes this all happen!  By allowing us to reference a starting point, and ‘move away’ or ‘offset’ from there by (1) a number of rows and (2) a number of columns, we can get exactly where we need to go on the spreadsheet.  Cool!
 
So, how does it work?  In the video, I introduce you to the Offset method with a simple example.  Remember to stop the video here and accept my challenge: can you see what’s going to happen when we run the code?  If you’ve got the basic idea, take the time to ‘play’ with the basic construct and build your understanding and confidence.  Yes, at Tiger, we know that code is play!
 
Now, a steep rise in the difficulty level, are you ready?  I mentioned at the beginning that Offset brings together powerful techniques in Excel VBA.  We integrate them fairly quickly in the video – so make sure you have watched the previous videos in the Excel VBA Beginner Building Blocks series on variables and loops in Excel VBA.  Oh, you haven’t seen the For – Next Loop video?  It’s a secret one on the website – follow the link in the video description to access it.
 
Building the construct step-by-step allows us to create a list of numbers with just a few lines of code.  As I demonstrate in the video, it’s easy to ‘scale up’ this example to any length of list – such is the power of this mechanism.  I love the combination of the three techniques and how integrating the variable into the code makes the magic happen.  If you get it working, make sure you punch the air!  Then take the time to develop this example and master the VBA Offset method yourself.
See you in the final video of the Excel VBA Beginner Building Blocks series!
 
ABOUT THE EXCEL VBA BUILDING BLOCKS COURSE
 
 
Learn the fundamentals of Excel VBA in around an hour with Tiger's FREE Excel VBA Building Blocks course.  The course is based on Chris' 10 years of Excel training, consultancy and content creation, and teaches the basics of Excel VBA around 18 simple ideas or 'building blocks'.  In addition to the 6 basic videos on Excel VBA, on the website you can find 3 'metaskill' videos (to be uploaded through Summer 2021) about next level concepts that could unlock your potential with Excel VBA.  
 
A course handbook is provided for free that lists each of the Excel VBA beginner building blocks and includes a learning template for each session - make sure you download and use it to support your learning.
 
Use the link below to download the course handbook and access the learning template for this session.
 
 

The BEST Way To Move Sheets Between Files In Excel (MM LITE #2)

The BEST Way To Move Sheets Between Files In Excel (MM LITE #2)
 
Moving data between files is something people do in Excel all the time.  But, it can be time-consuming and frustrating, particularly if it’s a task you have to do in Excel everyday or every week.  So, what’s the best way to do it?
 
 
Excel offers multiple options from the manual ‘copy sheet’ operation to Power Query, VBA code and others.  In this video, I demonstrate the basic ‘manual’ method, then set up and compare two ‘automated’ options using (1) VBA and (2) Power Query.  Either can save you huge amounts of time; which suits you best depends on your particular Excel situation.  So, let’s get into it!
 
CLICK HERE TO WATCH THE VIDEO

TIMESTAMPS
00:00 Excel VBA vs. Power Query Introduction
00:26 Manual Sheet Copy Demonstration
01:06 Record VBA Code To Copy Sheet
03:41 What Is Power Query?
04:33 Import Data With Power Query Demonstration
06:46 Discussion: VBA Or Power Query?
07:56 Access An Exclusive MM 1-Hour Tutorial
 
By right-clicking on the sheet tab at the bottom of Excel, you can access the ‘move and copy’ dialogue box by selecting ‘Move or Copy’.  I demonstrate this in the video with an additional challenge: I try to do it using keyboard shortcuts!  I’ve always found this dialogue box fairly easy to understand and use – you can move sheets around a file, to another file, and have the option of copying a sheet before moving it, too, so it is duplicated in both locations.
 
So, what’s the problem?  Well, even this manual process becomes onerous if you have to do it multiple times!  Moreover, it’s prone to human error.  There’s an additional consideration here too: do you actually need multiple files?  And, might duplicating the worksheet like this cause problems later?  
 
 
As with everything in Excel, the true answers lie at the ‘design’ level: the number of files you’re using (could you consolidate?), the number of sheets in each file, and the role of each.  Seriously, thinking about overall design saves so much time and stress: you might not need a fancy Power Query data link or VBA routine at all …
 
Let’s assume we do, at least for now.  If you’re using desktop Excel, the next option is to automate this sheet copy / move process using Excel VBA.  What, you don’t know VBA code?  No matter.  One of the benefits of VBA is that code is easily generated using the macro recorder.  This is what I do in the video: set the recorder going, do the actions, then stop the recorder and view the code.  With a bit of experimentation, you may be able to ‘tweak’ the code to get it doing exactly what you need, without much prior knowledge of VBA.  Computer code is language and it can be intuitive …
 
Many people, however, don’t want to delve into VBA.  They don’t wish to get involved with computer code and perhaps they’re not using desktop Excel.  I can understand.  Power Query is a ‘powerful’ alternative that doesn’t require any coding.  You do, however, have to navigate the Power Query interface which can be intimidating.  It’s worth trying, however, because Power Query offers some useful options such as importing only filtered rows and even cleansing data before it comes into the file.
 
The downside of Power Query?  It creates a ‘data link’ between the two files.  Yes, that annoying ‘do you wish to update data links?’ notification when you open up Excel is now ‘in play’.  And, you’ll have to get the data link to update which, I have found, is not always consistent.  This, for me, is where VBA is advantageous because we can control the data transfer process at the click of a button without any annoying data links between the files.
 
If you’ve downloaded the files and worked along with me, you should now have a feel for the 3 data transfer options.  The manual option works well but will soon frustrate.  Whether VBA or Power Query suits you best depends on your circumstances.  Are you on desktop Excel?  And are you comfortable using some code?  Perhaps you’re excited by the prospect of learning some VBA?  If so, I would recommend the VBA option over Power Query.  Let me know what you think in the YouTube comments.
 
Enjoy this type of Excel VBA tutorial?  We do it twice monthly, live in our Members' Monday community.  Click below to ask the FREE taster area and view sample sessions.
 

Master Excel's 11 Logical Formulae

Master Excel's 11 Logical Formulae


Yes, knowing more formulae doesn’t make you better at Excel.  The fact is that Excel is a skill, so more knowledge (eg, ‘I know 100 Excel formulae!’) does not make you better, at least not immediately.  So why build your Excel formula vocabulary?
 


CLICK HERE TO WATCH THE VIDEO

00:00 Introduction
00:39 Excel Formula Trainer Tool
01:45  F Function
03:00 OR Function
04:20 AND Function
05:13  TRUE Function
06:30 NOT Function
08:10  XOR Function
09:42 SWITCH Function
12:12   IFNA / IFERROR Functions
13:40  IFS Function
15:54  SUMMARY: What to take forward, be wary of and ignore
 
I think of Excel as a language: as with human languages, the more words you know, the better and more precisely you can express yourself.  This applies in Excel at least to some extent.  Yes, we know at Tiger you should be a value creator, not a technique collector, but knowing more Excel formulae can help, as part of a balanced learning approach.  And, Excel now has more than 500 formulae to learn!  With that said, let’s get into Excels’ 11 logical formulae …
 
 
Microsoft categorises formulae into 14 themes including ‘Database’, ‘Statistical’ and ‘Lookup and Reference’ (which is where favourites such as VLOOKUP live).  The groupings can help your learning: you probably don’t need to know the specialist Engineering formulae, for example, but a command of the main formulae in the main categories is essential.
 
Excel’s logical formulae allow us to express ‘logical’ relationships and are key to Excel modelling.  In this video, I’ll show which of Excel’s logical formulae to take forward, which to be wary of and which you can safely ignore.
 
First, the IF formula allows you to access a critical programming idea: conditionality.  ‘If something is happening, do this; if it isn’t happening, do that’.  The first part (‘is this happening?’) is known as a logical test, or a ‘condition’.  The video comprises 9 ‘small challenges’ that we tackle using the


How To Create A Loop Within A Loop In Excel VBA (MM LITE #1)

How To Create A Loop Within A Loop In Excel VBA (MM LITE #1)


How can Excel VBA help you do a typical manual task at the click-of-a-button?  We're all tired of repetitive manual work in Excel!  

In this video, I show how you can use a loop within a loop in Excel VBA to transform a dataset in Excel.  This task might take hours manually; with the right VBA skills it takes less than a few seconds - after you've written the Excel VBA, code, of course!  It's a real-world task requested by a channel viewer.

CLICK HERE TO WATCH THE VIDEO

Use the timestamps below to help you navigate this Excel VBA tutorial.  Though it can be complicated to set up a 'loop within a loop', note that the usual Excel VBA techniques are used here: loops, variables, position control and a few others.  More important, however, are the Excel VBA 'metaskills' shown in the video: note how Chris plans the task first then tests and debugs the routine as it grows.

 
Timestamps

00:00 Excel VBA Tutorial Introduction
00:34 Description Of The VBA Task
00:41 Planning And Conceptualisation Of The VBA Task
01:04 Create A New Macro
01:29 Declaring A VBA Variable
01:54 Create The First VBA Loop
02:39 Combine A Loop With The VBA Offset Method
03:34 Combine A VBA Variable With The Offset Method
04:59 Declare A Second VBA Variable
05:09 Embed One Loop In Another Loop In VBA
05:19 Annotate A VBA Routine
07:39 Initialise A VBA Variable
08:29 Increment Up A VBA Variable With a = a + 1
10:09 Experimenting With Different VBA Loop Control Values
10:49 Use A Named Range As An 'Anchor Point'
12:09 Explanation Of The Complete Line Of Code
12:44 How To Access Secret Excel VBA Tutorials!
 

The MM LITE series features shortened videos from our exclusive Members' Monday community live sessions.  Want to watch a full one-hour tutorial from our Members' Monday Excel VBA learning community, for free?  What are you waiting for?  Sign up for the FREE MM Taster sessions here:

In Members' Monday you get 2 monthly live sessions with Chris and a small group of like-minded learners, access to an exclusive community forum and the unique 'content navigator' tool to get you where you need to go in the MM content library.  It's a unique Excel (VBA) learning resource designed for serious learners only that will take your data analysis to the next level.


I Tried To Learn 500 Excel Formulae

I Tried To Learn 500 Excel Formulae (TAKE THE TEST!)


With the new Excel formula releases coming soon, there will be more than 500 formulae in Excel.  Yes, 500!  But, does the typical professional need to know all of them?  
 
In today’s video, I explain how I have been learning new Excel formulae, show you the custom Excel VBA tool I have used to help me, and help you prioritise the formulae you do and don’t need to know.

CLICK HERE TO WATCH THE VIDEO

If you’re looking to learn new Excel formulae, you’re going to love the Excel Formula Quiz tool.  As I demonstrate in the video, using only Excel on your desktop, you can test your knowledge of formulae and gradually build your Excel vocabulary.  Got a colleague who is using Excel too?  Why not share the file with them and settle once and for all who the better Excel user is?!

Seriously, the Formula Quiz tool has helped me build my Excel formula vocabulary recently in a fun an interactive way.  You’re going to love it, and it’s available for download absolutely free here:

FREE DOWNLOAD: Excel Formula Quiz Tool
Oh, and if you’re interesting in how I built it, I’ve created a 2-hour walkthrough video as part of our free Excel VBA Building Blocks course, check it out!

So, do you need to know 500 Excel formulae?  The truth is, probably not.  You’ve heard me say many times on the channel that there’s a subset of formulae and techniques you need to know – I cover them in my Excel Cheatsheet which you can download here.

The concept of ‘category’, however, could help you find a path through this forest of Excel functions!  Microsoft categorises the formulae according to their usage – Lookup and Reference, for example, contains VLOOKUP and its friends.  Other categories relate to specific vocations such as Engineering.

As I explain in the video, if you’re not a specialist in one of the categories (eg. A mathematician or finance specialist), I suggest focusing on the first five categories only.  They comprise around 100 formulae and will give you a very firm foundation in Excel and data analysis.  Don’t learn them all, however!  Perhaps pick a few from each category to get started, then let the utility of the new formulae guide your learning … Or, simply download the Excel Cheatsheet to access the specific subset of Excel formulae and techniques that I recommend.

At the very least, I hope you’ll download the Excel Formula Quiz tool and have a go!  Let me know how you get on in the YouTube comments and let’s start a conversation there.

 

Get Started With Loops In Excel VBA- For each object in a collection

Get Started With Loops In Excel VBA


Loops in Excel VBA are, quite simply, the best way of taking your coding to the next level.  They allow us to do something extraordinarily powerful: repeat a command any number of times.  They’re not easy to programme, however, and you’re likely to get stuck in an interminable loop at least once - it happens!  So, how should a beginner learn loops in Excel VBA? 
 
 
 
CLICK HERE
TO WATCH THE VIDEO

Forget about Excel for a second and let’s deal with the concept first.  Take this completely implausible, fictional situation: I have a biscuit tin on my desk that I use to store used batteries (content creation involves many, many AA batteries!)  I intend, one day, to recycle these batteries which is why I keep them in the biscuit tin. 
 
 
Suppose I need to mark these batteries as ‘used’ (I often confuse used with non-used batteries.)  I have a permanent marker for the purpose and instruct YOU to do the job (suppose you are the Tiger Intern for a day – a terrifying prospect, I know!)  I request via email that you undertake the task.  The email reads as follows: 
 
Hi Tiger Intern, 
Here’s a job for you 
Write ‘USED’ on battery 1 
Write ‘USED’ on battery 2 
Write ‘USED’ on battery 3 
… 
Write ‘USED’ on battery 25 
Thanks, 
Chris 
 
What’s wrong with this email?  Well, it’s completely unnatural.  I’ve communicated the task in a verbose (and quite frankly ridiculous) manner. 
But, here’s a thought: are currently going about your coding in this inefficient way? 
 
How would you actually ask somebody to do the above?  Rather than referring to each battery individually, you would simply ask: 
 
'Write ‘USED’ on each battery in the biscuit tin' 
 
Let’s quickly analyse the architecture of this sentence, because it unlocks understanding of loops in Excel VBA. 
 
There’s a containing whole, what Excel would term a ‘collection’ of objects that are essentially the same (ie. the batteries).  So, we have the concept of a collection of objects.  And, we wish to do something to each object in the collection.  There it is!  This is the language we use to structure loops for beginners n Excel VBA: 
 
‘For Each Object In A Collection’ 
 
So, by harnessing Excel’s language and understanding, at least at a basic level, how Excel organizes things into collections of objects, we can begin to access the power of loops. 
 
In the video, I show how to use the above construct to do a simple task in Excel: display the names of all worksheets in the file.   Hopefully, you’ll punch the air when it works!  We’ll also look at other easy examples of how to use this basic idea to get you using loops in Excel VBA. 
 
ABOUT THE EXCEL VBA BUILDING BLOCKS COURSE 
 
 
Learn the fundamentals of Excel VBA in around an hour with Tiger's FREE Excel VBA Building Blocks course.  The course is based on Chris' 10 years of Excel training, consultancy and content creation, and teaches the basics of Excel VBA around 18 simple ideas or 'building blocks'.  In addition to the 6 basic videos on Excel VBA, on the website you can find 3 'metaskill' videos (to be uploaded through Summer 2021) about next level concepts that could unlock your potential with Excel VBA.  
 
A course handbook is provided for free that lists each of the Excel VBA beginner building blocks and includes a learning template for each session - make sure you download and use it to support your learning. 
 
Use the link below to download the course handbook and access the learning template for this session. 

Beginner Introduction To Variables In Excel VBA

Beginner Introduction To Variables In Excel VBA


If you’re a beginner in Excel VBA, you may be wondering: What’s An Excel VBA Variable?

Excel, like computer programming more generally, is full of terms that are difficult to understand.  I’ve often said, for example, that the OFFSET formula should be renamed ‘MOVE AWAY FROM’.  So, what about a ‘variable’?  Does it ‘vary’ in some way?  Well, kind of.  But, as you might expect, its function cannot be easily understood from its name.  It’s a shame because variables in Excel VBA are one of the building blocks of powerful Excel applications.

I’m offering you a simple definition today: a variable is a place to store information.  In this way, it can be compared to a cell in the spreadsheet – we are very used to inputting data there!  So, why do you need variables?  Well, unlike cells, variables live in VBA and are only ‘seen’ if we specifically ask to externlise their value for some reason (for example, by using a message box, as we do in the video.)  This means they can be more efficient and easier to control - variables drive all the ‘slick’ Excel VBA applications I have developed over the years.

But, you’ve got plenty of time to get to the more powerful applications later.  In this video, I take you through variables from an absolute beginner perspective.  We explore the concept of a variable (look out for the ‘mic pack’!), create three variables in Excel VBA, then pass data to them and use them to perform a simple operation.  In the final step, we see how variables can ‘read’ and ‘write’ data to the spreadsheet.  In this sense, they facilitate a thing of beauty and a feature of many powerful Excel applications: interaction between the spreadsheet and the VBA editor.

ABOUT THE EXCEL VBA BUILDING BLOCKS COURSE

Learn the fundamentals of Excel VBA in around an hour with Tiger's FREE Excel VBA Building Blocks course.  The course is based on Chris' 10 years of Excel training, consultancy and content creation, and teaches the basics of Excel VBA around 18 simple ideas or 'building blocks'.  In addition to the 6 basic videos on Excel VBA, on the website you can find 3 'metaskill' videos (to be uploaded through Summer 2021) about next level concepts that could unlock your potential with Excel VBA.  

A course handbook is provided for free that lists each of the Excel VBA beginner building blocks and includes a learning template for each session - make sure you download and use it to support your learning.

Use the link below to download the course handbook and access the learning template for this session.