Author Archives: Chris Mortimer

.Cells For Beginners

Let’s learn the Excel VBA .cells technique

Click here to download the STARTER file used in the series.

Click here to download the COMPLETED file used in the series.

The Excel VBA .Cells technique is incredibly powerful – and slightly confusing! Learn it and use it with this series of four videos.

TUTORIAL VIDEO 1

In the first video, we deal with the basic concept and apply .cells to select a single cell. The .cells technique uses numbers to reference cells in the spreadsheet; this makes sense for rows (we are used to using numbers to refer to them) but less so for columns. So, we first use an =column formula to show how Excel can reference columns using numbers. Cool!

After opening up the VBA editor, we attempt a simple application of .cells. Chris explains the basic cells(a,b) concept where ‘a’ determines the number of rows down, and ‘b’ the number of columns across. Armed with this concept, you can use .cells to select a cell in the spreadsheet. Remember to try different combinations – it should feel like you are ‘playing’ with the code; this really is the best way to build confidence.

Finally, we introduce the idea of the ‘anchor point’. By integrating a cell reference before .cells (eg. Range(“A1”).cells(1,1)), we can ‘anchor’ the cells technique anywhere on the spreadsheet. Chris demonstrates this by using A1 as an anchor point. Those experienced with .offset will notice a subtle but important difference in the referencing here, which Chris explains in the video.

Hopefully you are beginning to feel the power of the .cells technique! How did you get on, and what applications have you found for .cells in your work? Please leave a comment.

TUTORIAL VIDEO 2

Having learned the basic concept in video 1 (above), we explore a more complex application in video 2. We witness what Chris terms ‘Poetry in Motion’ – spreadsheet cells and VBA code working together to create dynamic functionality. A profound capability indeed – but how does it work in this context?

We are familiar with the .cells(rows, columns) basic construct. In the first video, we used ‘hard-coded’ values, inputted in the VBA editor, in the rows / columns components, to select cells. But we can do better than that – nobody wishes to access the VBA editor every time they need to tweak a function! In the second video, we substitute the ‘hard-coded’ values (which are best avoided in Excel VBA) for cell references.

What does this do? It means that we can control the selected cell *from the spreadsheet*, with no need to open the VBA editor at all! This interplay between the spreadsheet and VBA editor is what Chris terms dynamic functionality; it is a feature of many powerful VBA routines. In the video, Chris sets up the mechanism and does some testing and experimentation (‘play’) to build confidence in its robustness.

Ah, Poetry in Motion … 🙂

EXCEL VBA .CELLS SERIES RELEASE DATES

Video 1 – 08/03/19 – https://youtu.be/x_PCdMlO41M
Video 2 – 22/03/19 – https://youtu.be/tlmFmcimIwU
Video 3 – 05/04/19
Video 4 – 19/04/19

Playlist link:
https://tinyurl.com/y46hxw5h

These introductory videos will be supported by ‘long-play’ application videos (below) showing how to apply the technique to real-world problems. These will be viewable in the series playlist when released.

APPLICATION VIDEO 1

Click here to download the STARTER file used in the video.

Click here to download the COMPLETED file used in the video.

This video is a viewer request (thank you to Hugo) and an example of the power of the VBA .cells technique.

In the video, we combine .cells with two different kinds of loops to get a mind-boggling amount of work done at the click of a button.

The briefing asks for every sixth row to be copied to a different sheet for analysis. At the beginning of the video, Chris puts together the main elements of the routine (.cells, a loop, conditional statement), and introduces the VBA Mod function to identify every sixth row on the worksheet. Two integer variables are used to control position: they synergise beautifully with .cells to create the required mechanism.

Next, we explore how to delete ‘unwanted’ rows. Chris gets in a coding tangle and, in the end, implements two different loops to get the job done. The second loop uses Do Until to repeat an instruction until an empty cell is found, ie. until the bottom of the dataset. Though the code is functional, it is CPU-intensive, and takes a few seconds to execute. We conclude that moving target data to another area can be preferable to asking VBA to delete rows.

We hope this application video helps you feel the power of the VBA .cells technique. What have you managed to do using .cells? Leave a comment on the YouTube channel, we would love to hear from you!

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Your First Pivot Table

Let’s create your first pivot table

Click here to download the file used in the video.

Work along with Chris to create your first pivot table.

Excel Pivot Thumb

Pivot tables can be scary! But, if you are not using pivot tables, you are missing out on Excel’s most powerful data analysis facility. Not only can you produce powerful analyses with a few button clicks, but pivot tables will reduce sorting, filtering and fragmentation of data across sheets.

In the video, Chris shows you how to create a simple pivot table. The data comes from the YouTube channel and Chris wishes to examine two metrics at the same time: traffic sources and countries.

The first step is to visualise the pivot table: what should appear in the rows, what should appear in the columns, and what data should appear in the table? At this point, it is a good idea to write down the specific question you are looking to answer. Chris recommends using ‘good old pen and paper’ to formulate the key questions and (literally) sketch out the table layout.

Next, we can select the target data and insert the pivot table. A critical mistake at this point is to exclude the column headers from the selected data; make sure you include the column headers in the selection. The create pivot table dialogue box allows you to position the table on a new or existing sheet – we recommend using ‘new sheet’ for the timebeing.

Now, time to create the table! It can be confusing when you click ‘ok’, yet no table appears. Rather, we are confronted with a blank space labelled ‘pivot table x’, and an unfamiliar interface. Chris shows how to quickly manipulate the interface to get the analysis into the layout you require. Cool!

Did you manage to create the pivot table? What is your application of pivot tables, and how have they helped? Are you experiencing any particular problems? Leave a comment below the video.

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Uncategorized | Comments closed

19 Excel Beginner Shortcuts for 2019

Let’s Learn Some Excel Keyboard Shortcuts for Windows Excel

Click here to download the file used in the video.

Excel keyboard shortcuts can save us a huge amount of time.

Chris has selected some of his favourites for you to learn in 2019.

Excel Shortcuts Pic

List of Excel Beginner Shortcuts covered in the video:

1 Alt + F + I + E = Enable editing
2 Arrow keys = Basic navigation
3 The F2 Key = Edit cell
4 Ctrl + Z = Undo
5 Alt + H + O + W / H = Resize rows and columns
6 Alt + H + B + A = All borders
7 The F4 key = Repeat last action
8 Alt + H + E + F = Clear formats
9 Ctrl & Arrows = Go to end of range
10 Ctrl & Space / Shift & Space = Select column / row
11 Ctrl + A = Select region
12 Alt + A + S + S = Sort
13 Alt + H + D + R/C = Delete row / column
14 Ctrl & Alt &+ V = Paste special
15 Ctrl & D / Ctrl & R = Autofill down / right
16 Ctrl & Page Up / Down = Switch sheets
17 Alt + W + V + G = View gridlines
18 Alt + W + F + F = Freeze panes
19 F12 Key = Save As

How are you getting on with keyboard shortcuts? What is your favourite shortcut not mentioned here? Leave a comment below the video on the YouTube channel.

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Uncategorized | Comments closed

Understand an Excel File in 3 Steps

How can you easily understand an Excel file?

Click here to download the file used in the video.

We have all been there. Our boss hands us a ‘tricky’ spreadsheet file, and tasks us with ‘understanding’ it. Sounds simple enough, until you open it to find it contains no fewer than 34 sheets, has no consistency between sheets, and no sense of a beginning and an end!

How do you even get started?

Here is the three step process I work through to understand a complex Excel spreadsheet.

1. Get an Overview

It can be tempting to get stuck into the detail, but to do so can compound your frustration – and you are probably already frustrated enough. Rather, try to get an overview of the file first. Working from out to in like this allows you to understand the role of each of its parts.

How do we get an ‘overview’? The biggest building block in an Excel file is a sheet, so let’s first try to ascertain how many sheets are in the file. That means a quick glance at the sheets tabs, right? Yes, but there may be more sheets in the file that are ‘hidden’ or, in particularly tricky cases ‘very hidden’!

First, right-click on one of the sheet tabs at the bottom of the screen, and click ‘unhide’. If unhide is shaded out then great, there are no hidden sheets. If you can click unhide, you will next be presented with a list of hidden sheets in the file. You can click and unhide the sheets individually; but, this is onerous if there are more than a few, so what are the alternatives?

Keyboard shortcuts can help. On a Windows PC, you can use the Alt + H + O + U + H shortcut (hit the keys sequentially, not simultaneously) to speed up the process. Or, if you are comfortable with VBA, you can use a macro to unhide all sheets in the file at the click of a button! The code is included in the download file (link above) – copy / paste into the VBA editor for your file and click the play button to run the routine. Cool!

Real spreadsheet fiends may go to great lengths to hide a sheet. It is good to know that the .visible property of an Excel worksheet can be set to ‘very hidden’, which means that you can’t unhide it in Excel. But, the sheet does appear in the project explorer in the VBA editor, which can confuse! (It has confused me more than once). All sheets will be unhidden if you run the above macro, including the ‘very hidden’ ones. Phew! And people out there who are making sheets ‘very hidden’, we would really rather you didn’t 🙂

All this clicking around the spreadsheet and VBA editor can seem time-consuming. But, it gives us what we absolutely need to de-stress the process – an overview of the whole file, and a sense that it cannot get any worse, because everything is now viewable. Onwards!

At this point it is important to check if the file has external links. Click the Data tab and ‘edit links’. If there are links to other files, you will have to repeat this process with those files too. Sorry, I did not say this would be easy!

Three Steps

2. Click around and categorise

Click on a worksheet and start clicking through some of the cells to ‘get a feel’ for what is going on. This can create a lot of cognitive load. Try not to get stressed and simply take a deep breath if it gets confusing, you will work this out in the end!

The following simple idea helps me see through the formula fog:

Almost all cells in any spreadsheet fall into one of three categories:
Input cell
Process cell
Output cell (including pivot tables)

Simply click through and begin to categorise the cells. What do the categories mean?

An input cell contains a value, as opposed to a formula. This means a number that has been inputted or copy / pasted to the cell. You can confirm this by double-clicking on the cell (or by navigating to the cell and hitting the F2 key on a windows PC); if no formula appears, this cell contains a value, and it is an input cell. An input cell has no precedents, it does not depend on any other cell. You could call it a ‘starting point’.

A process cell contains a formula. It supports the calculation chain or ‘process’ of the Excel file. You can identify a process cell by establishing if it contains a formula or not. If you can, make a mental note of the cell(s) on which the formula depends. Keyboard shortcuts will help here: on the Windows PC, F2 puts you in formulae editing mode, which allows you to see precedent cells nearby, and ALT + M + D will show cells dependent on this cell. If a dependency arrow points to another sheet, you can find out which sheet it points to by clicking on the base of the arrow.

An output cell, like a process cell, contains a formula. The critical difference with an output cell is that it has no dependent cells – it is the end of the calculation chain, the end of the spreadsheet, the end of the world! Ok perhaps not, but finding one of the these can be exciting because output cells represent an objective: It is the Excel file’s purpose to display them clearly. They should be prominent in a *well-designed* spreadsheet file; located in a dashboard, or at least at the top of a sheet. Bear in mind most spreadsheets are not well-designed, however… Nevertheless, output cells give us a clue about what the creator of the file was trying to achieve.

We can categorise pivot tables as output cells. How to spot a pivot table? The default pivot table formatting is usually the best identifier. You can confirm this by clicking on the cells and noticing if pivot table options appear. Also remember that charts usually point to output cells, since they communicate analysis that is the main focus of the file.

With the concepts of input, process and output you should be able to categorise most, if not all, of the cells in the spreadsheet file. This should give you an overall impression of what is going on. Focus on the general direction of the calculation, rather than the specifics, at this stage. Next!

3. Tabulate or Visualise

How to organise and retain these insights? With multiple sheets, and hundreds or thousands of dependencies, it is a lot to keep in your head. I recommend creating some kind of visualisation. The simplest option is a three column table with headers ‘input’, ‘process’ and ‘ouput’ under which you list the main sheets / cells that fall into each category.

I create structural diagrams of whole files with each sheet represented by a rectangle, with a short summary of the information on that sheet. You can colour code to distinguish input sheets from process and output sheets, assuming the Excel file is sufficiently well-organised! Or, depending on how the file is set up, you might distinguish different blocks within the same sheet. Whichever level you work on, creating a visualisation can really help the process; moreover, your boss, client or teacher is likely to love it! People tend to like talking spreadsheets if it does not involve looking at … a spreadsheet.

Unfortunately, there is a sting in the tail. By understanding the process cells, we have fully understood the calculation chain, right? Not quite. Cell interactions are often communicated through formulae, but, as any subscriber to the Tiger channel knows, they can also be programmed in VBA! If macros are creating interactions between cells, these interactions are not discernable in the spreadsheet. There is no substitute for opening up the VBA editor and interrogating the code. Once again, I told you this would not be easy!

How to understand what the code is doing? This should be familiar territory for those who have worked through Tiger’s videos: the F8 key on a Windows PC allows you to ‘step into’ the code and understand what is going on. Don’t forget you can search the VBA editor (CTRL + F on a Windows PC) to find important references such as output cells.

Bear in mind that code can be organised into modules, but might also be assigned to worksheets in the VBA editor – if a routine is triggered on a worksheet change, for example. Look out for custom VBA functions; unlike routines, that execute without an apparent trigger mechanism. They can be particularly fiendish and difficult to understand. But, at least you now know to look out for them!

So that’s it: my three-step guide to understanding any Excel file.

Is it a straightforward process? 99% of the time, it is not. It takes serious mental engagement to work out a complicated file, which is why a lot of people tend to give up. The above road map should keep you on track and help you navigate the main features, and retain your sanity. It won’t be easy but, once you have mastered the file, you can embark on a redesign to make it easy for the next person (!)

Good luck!

Have you tried this approach? Did it work? Did you generate any new insights? Leave a comment below the video on the YouTube channel.

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Most Important Skill in Excel VBA

What’s the Most Important Skill in Excel VBA coding?

It might not be what you think.

VLOOKUP Problem Thumb

BY CHRIS MORTIMER

I have the privilege of teaching university students about Excel and VBA coding about one day a week, at a top-10 university here in the UK. I’ve been doing it for almost 10 years now, and I love it!
It’s usually fun and it’s energising for me to see others get excited about the potential of Excel VBA.

My approach to teaching the subject is simple: to trigger that initial curiosity and help them appreciate the possibilities; once these things happen, students tend to get very enthusiastic about VBA very quickly.

That’s as long as they have one critical skill. It has fascinated me over the years watching people grapple with coding for the first time. What I have found is that those from technical backgrounds are not always the best VBA coders (although they are usually pretty solid!) At the same time, the process throws up some surprise success stories: people from social science or arts backgrounds who excel at coding.

Why this discrepancy? One would expect Excel capability to be correlated to previous experience with other coding languages and logic-based subjects such as mathematics. Though this holds as a generally rule, there are enough notable exceptions to fuel other theories about coding success. So what quality do all successful coders share?

All successful coders have a set of attributes that you could bundle together and describe as ‘resilience’. Yes, you’ve got to be ‘resilient’! These attributes are not earned through study, in fact they are not technical at all. Everybody has them to a greater or lesser extent, and this article might motivate you to strengthen them. My dog has them! Let’s dig into what these attributes actually are…

First, you’ve got to react well when things go wrong. I’m talking about an initial reaction here. You can be sure of this: Excel VBA is going to throw up errors (particularly when you’re less experienced) that will confuse you, and how you react is important.

Naturally you might feel frustrated, tempted to close the laptop, or throw it out the window – I’ve been there! More importantly, you’re probably thinking things like ‘I’m just not a coder’ and ‘I don’t have the background to do this’. Though understandable, you have to avoid this reaction; to avoid it is to demonstrate resilience. So, rather than giving up, say to yourself ‘I can fix this’ (yes, say it out loud, I always talk to myself whilst programming!), then begin the process of debugging.

The ability to respond positively when everything goes wrong can sound like a big deal, but it is actually hard-wired into our psyche; it is just a case of reactivating this part of your brain. All of us have been through tough learning experiences: learning to walk, learning to ride a bike, learning an instrument, a sport, or something else. I compare VBA learning to these things because, for me, Excel is ‘play’; things are going to go wrong – but it doesn’t matter, we’ll fix it. Over time, try to assume this mindset, it might allow you to feel childlike enthusiasm for the topic (I know I do, can you tell? :-))

Second, you have to be able to ‘debug’. What does this mean in a practical sense? At the basic level, you need to be able to arrange the VBA editor and Excel alongside each other so that you can ‘step through’ the code and see what is happening in Excel. It always surprises me how few people can do this quickly – you do it instantaneously use the Windows key + left arrow shortcut on a Windows PC.

Next, you need to know what Excel VBA’s debugging aids are. You can find them at the top of the VBA editor in the ‘Debug’ menu. Excel VBA provides the helpful facility to ‘step through’ the code line by line (F8 key on the Windows PC.) You can also use breakpoints to stop a routine mid-execution and understand what is going on. Don’t forget you can hover the cursor over VBA variables to see what values they hold. Command of such techniques is critically important and you can see me use them in most of my videos.

In the fullness of time, you will begin to enjoy debugging because you know you will always fix it in the end, and some learning will result (it’s play!) This mindset, founded on a bundle of capabilities that I’m calling ‘resilience’, is one sign of a resilient programmer.

How else can you recognise a resilient programmer? Back to teaching, we offer ‘tutorial’ sessions for students where they work through problems with teachers on hand to help them. In the first few weeks, these sessions are quite chaotic because students ask questions all the time. Hands are going up everywhere! The reaction when things go wrong is to disengage and seek outside help.

Through the term, I gently and consistently reinforce messages about dealing with problems. Reacting correctly, using the debugging aids, developing the critical skill of resilience. This sidelines the teacher in the learning process, so why would I advocate this? As a teacher, is that not counter-intuitive? In my view, the best learning a teacher can offer a student is motivation to develop self-reliance and resilience. I want to feel, as a teacher, that I am gradually making myself redundant as students learn to deal with problems on their own.

Over time, the number of questions reduces, despite the fact the topics get more difficult. Then I know at least some students, equipped with right skill and mindset, are off and running on their programming journey…

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Merry Christmas! Excel VBA Advent Calendar

Merry Christmas! Let’s build an advent calendar using Excel VBA…

Click here to download the starter file used in the video

Click here to download the completed file (version 1)

Click here to download the completed file (version 2)

VLOOKUP Problem Thumb

BY CHRIS MORTIMER

Need to look seasonal but don’t wish to invest in a ‘physical’ advent calendar?

Want to take your VBA coding to the next level?

If either (or both!) apply, this full Excel VBA lecture could be for you.

Our Excel VBA advent calendar dispenses sweetness in the form of words, not chocolate (so it is healthy too!) It takes lyrics from the popular Christmas tune ‘Stay Another Day’ by UK pop group ‘East 17’, and presents a lyric (or some lyrics) from the song when the user clicks on a day icon. This song is Chris’ personal favourite, but you could substitute lyrics from any other song.

The calendar comprises basic and advanced functionality. In basic mode, the calendar displays a single lyric; in ‘multi’ mode, the calendar displays all lyrics up to the day clicked. And there is more. A validation control checks to see if the user is attempting to ‘open’ a day too early! We grant the user the option to switch off this control to allow for glorious ‘full song’ lyric viewing from the beginning of December. Well, it is the season of good will…

Seriously, this kind of creative application provides a perfect learning space away from the pressures of deadlines, customer expectations and file specifications. In the video, I explain how to apply variables, loops, conditional statements and more to get this job done. You’ll see me get into a coding tangle on more than one occasion, and work around the issues using VBA’s debugging aids.

I hope this video is a fun diversion from more serious matters, or another step in your Excel learning journey. Either way, me and the team at Tiger wish you a Merry Christmas. I hope you’ll be back with us in 2019 for more Excel fun.

Take care,
Chris

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Advanced Articles, Beginner Articles, Excel Guidance Articles, Intermediate Articles, Uncategorized | Comments closed

VLOOKUP Formula Problems / Fixes

Let’s Fix Your VLOOKUP Formula

Click here to download the file used in the video

VLOOKUP Problem Thumb

VLOOKUP formula problems? There is no doubt about it, VLOOKUP can be infuriating. But, it is an incredibly powerful formula. For many, it is the first ‘non-trivial’ Excel formula learned: those who master it tend to go on to learn many other powerful techniques; those who do not may give up serious Excel learning. In this way it can have a kind of strategic importance in the Excel learning journey. So, let’s fix yours, ASAP!

PROBLEM 1: SPELLING (EXTRA SPACES?)

A simple problem accounts for most problems with VLOOKUP. Spelling generally has to be 100% accurate. Make sure the entries in the database are accurate, as well as the value you are looking up. Also, do you have extra spaces after your entries? This classic mistake is difficult to spot and can be infuriating. Precise spelling clears up a lot VLOOKUP formula problems.

PROBLEM 2: LACK OF ABSOLUTE REFERENCES IN TABLE REFERENCE

It is natural to want to ‘drag down’ a VLOOKUP formula to apply it to multiple cells. To do so can create problems if the correct referencing technique is not used. When copying the formula to other cells, we wish to ‘fix’ the table reference, so that all cells refer to the same table. This necessitates use of absolute references or ‘the dollar signs’. See how Chris integrates them in the video.

PROBLEM 3: NOT REFERENCING THE ‘FURTHEST LEFT’ COLUMN

VLOOKUP requires a table reference and does only one thing with it: it tries to find the look up value in the leftmost column of the table. It cannot find a lookup value in the ‘second-from-left’ or any other column! Make sure the leftmost column of table contains the lookup values – you may have to adjust the table reference, or the layout of the data.

PROBLEM 4: LOOKUP COLUMN OUTSIDE OF TABLE RANGE

You might wish to adjust the VLOOKUP formula to return data from another column, or add an additional VLOOKUP formula to do the same thing. It is important to remember that VLOOKUP can only find columns within the table range, however. An #REF! error may mean the proposed column index number exceeds the number of columns in the table. To resolve this, review and enlarge the table reference to encompass more columns.

PROBLEM 5: LACK OF UNDERSTANDING OF ‘APPROXIMATE MATCH’

The problem that is most difficult to explain! People often avoid dealing with the last component of the VLOOKUP formula that determines the match type, which we can set to exact or approximate, by excluding it altogether. It is best to include it, to make things clear to Excel and to other spreadsheet users, and for your own learning benefit! Before we go further, it’s worth noting that, in most cases, exact match (FALSE or ‘0’) is most appropriate; this is because people are usually trying to match ‘exact’ text values (see the example in the video). If in doubt, set this value to false (‘0’).

So, what is approximate match for? VLOOKUP with approximate match helps is slot a number into a range of numbers. The values we input in the leftmost column of the table act as ‘lower bounds’; Excel constructs ranges and slots the look up value into the appropriate one. This can be difficult to envisage: check out the example in the video, and download the example file.

So, did you manage to fix your Excel VLOOKUP problem? Thanks for reading and leave a comment on the video.

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta

Contact us here.

Posted in Advanced Articles, Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel VBA to Copy Data to Sheets

Speed Up Onerous Copy / Pasting in Excel Using VBA

Click here to download the STARTER file used in the video.
Click here to download the COMPLETED file used in the video.

Excel VBA Copy Data to Sheets Chris Only

BY CHRIS MORTIMER

Have you ever had to copy / paste data in Excel from one sheet to another? Perhaps you’ve had to copy / paste certain rows from a dataset to separate sheets. I am pretty sure you are not the only one who has found themselves frustrated in this situation. It takes SO much time! Surely there is a better way…

We can use Excel VBA to copy data to sheets in Excel. In the video tutorial, we take an example (sent in by a subsriber to the Tiger mailing list) from the world of horse racing. The workbook contains details of horses in races from the past few years; the user wishes to be able to copy the horses from each race onto a separate sheet. Sounds simple enough, right? It’s a typical example and I’m absolutely sure you will have tried to do something similar at some point in your working life – I know I certainly have…

… in fact, I tried to do it for this tutorial (see the beginning of the video!). I copy / pasted a few entries onto separate sheets and was genuinely shocked at how much time (not to mention, energy!) this relatively simple task used up, and I soon gave up! I started thinking about how to exploit Excel VBA to copy data to sheets; the result was an awesome VBA routine that executes in just a couple of seconds. Yes, this manual task that takes up hours of your time can be reduced to just seconds.

In the video, I walk you through the code, step-by-step, using the same systematic coding approach I use in my day-to-day work. Some of the ‘usual suspects’ from the Tiger videos are there – variables, a loop, conditional statements, use of the macro recorder. The code is quite complex but, with some conceptual understanding, you will be able to make sense of it, and hopefully apply it in your work. If this video is too advanced, work through our ‘Excel VBA for Beginners’ playlist on YouTube first.

Good luck and let us know how you get on.

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel Frequency Formula: Common Problems / Fixes

Excel Frequency Formula: Excel’s Most Difficult Formula?

Click here to download the file used in the video.

BY CHRIS MORTIMER

I think of Excel learning as a journey, and I have certainly been on a journey with the Excel Frequency formula!

From being intrigued by it, confused by it, ignoring it altogether and then relearning it step-by-step, I have come to appreciate its unique power. We have to go through a number of steps to get it working properly – without these steps, it doesn’t quite work, which creates frustration and a reputation of being ‘difficult’. In fact, I think it is the most difficult Excel formula to get to grips with. It’s a real shame that many people, like me, have struggled with it: we are missing out on an Excel gem that creates powerful, visual analyses that quickly ‘tell the story’ of datasets.

Frequency Formula Chris Only

In this article and accompanying video, we explore five common issues with the frequency formula, and look at how to fix them. We explore the key concepts and critical technical issues. An appreciation of both will position you to take advantage of this incredibly powerful Excel formula. So, what are those five issues? Let’s dig in to number 1.

Excel Frequency Formula: Five Common Issues

1. CONCEPT NOT UNDERSTOOD (DATASET / BINS)

It is easy to blame Excel when things are not working – I have done so many times. The formula / coding are too complicated, the interface is not user-friendly etc. etc. In fact, when people struggle with Excel, the cause is usually conceptual, not technical. By that, I mean that we lack a clear idea in our own heads of what we are trying to do, or how the functions actually work.

I encourage the people I work with to ‘speak out loud’ what they are trying to do with formulae, and / or to articulate in words what a formula does. Technical proficiency (ie. being able to build the formula) comes when conceptual understanding is clear. In the first part of the video, Chris explains the two key concepts, datasets and bins, with reference to an example from the world of running!

2. BINS NOT UNDERSTOOD / ACCURATELY DEFINED

The Excel Frequency formula relies on the concept of ‘bins’ that we define in a single column as single values. Single values? How on earth does that work?! Surely Excel would need to know two values, the upper and lower bounds of the ‘bin’?

Fair question! Excel actually interprets the single values as ‘upper’ bounds and uses this idea to construct the bin ranges. The best way to think of the single values in the bins column is ‘upper bounds’; and, the best way to understand this idea is to download the Excel file and check out the ‘Explainer’ sheet. Bear in mind that the formula looks at the single values in column B, not the ranges as defined in column C – these are included as a learning aid.

This bit can be difficult – it took me years to work out. Take your time and expect some frustration.

Now we have grappled with the conceptual side, let’s consider the technical points…

3. FORMULA ENTERED INTO ONE CELL / ABSOLUTE REFERENCES ($) NOT USED

4. CTRL + SHIFT + ENTER ENTRY TECHNIQUE NOT USED

Points 3 and 4 refer how to construct the formula and to ‘enter’ it into Excel. There are a number of critical points here and, if any one is ignored, the formula is unlikely to work. The only way is to step back, take a steady and systematic approach, and be ready to start again! Follow along with Chris’ detailed explanation in the middle of the video.

5. DIFFICULT TO AMEND / TWEAK = FRUSTRATION!

The final humiliation! Once entered into Excel, the formula can be difficult to remove / amend. This is because it is an ‘array’ formula and, as with data tables, we cannot delete only part of it. In order to remove the formula, we have to select all of the cells in which it is entered. If you need to amend the formula, I recommend deleting all of the formulae and ‘starting again’. I told you it might not be straightforward!

Download the example file, work along yourself, and feel the power of the Excel Frequency formula. If you can get it working, the rewards are immense: the formula helps you to quickly understand the main features of a continuous numerical dataset. As Chris demonstrates, the formula can be used as the basis for chart that is bound to create value for your clients. Good luck!

How did you get on with the Excel Frequency formula? Is it really ‘Excel’s most difficult formula’? Leave a comment on the YouTube channel.

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Excel VBA to Convert Minutes to Decimals

An instructive example of the power of Excel VBA

Click here to download the file used in the videos.

BY CHRIS MORTIMER

From time to time I come across an application of Excel VBA that perfectly sums up our message: that a bundle of critical VBA techniques, applied correctly, can transform the way you work. Yes, transform the way you work. We are not talking about saving a few minutes here and there, we are talking about hours and days saved, and a shift in mindset towards data and analytical work in general.

Excel VBA Min Dec Thumb

It’s a message I try to communicate in my work and on the YouTube channel; but, to be honest, I don’t feel that people often understand the potential impact of Excel VBA. If they did, they would be as excited as I am about the applications! No matter. All I can do is keep presenting examples of applications, and chipping away at popular misconceptions such as ‘code is for coders!’

Today’s example comes from a real-life Excel development project focused on an HR issue. Yes, as with most of the examples on the YouTube channel, this application is based very closely on a real-life example from my own work.

The Excel VBA I created converts time in hours and minutes to a decimal figure, in order to provide accurate aggregates of hours worked. For example, a four-hour-fifteen-minute shift would previously display as 4.15; when ‘decimalised’, this translates (inaccurately) to 15% of one unit. We know that there are 60 minutes in an hour; so, in this case, the decimalised value should equal 4.25. In the same way, 4.30 should be converted to 4.50, and 4.45 to 4.75. It’s a potentially time-consuming task that can frustrate; with the right knowledge, it can be quickly automated using Excel VBA.

A system is the product of its interactions

What techniques are required? Fans of the channel will be familiar with those used: (different types of) variables, loops and a conditional statement. It is the power of the techniques working together that I wish to emphasise. To do so, let’s use the concept of ‘cool points’. We have all seen, and felt ‘cool’ things in Excel. Remember when you first applied VLOOKUP, for example? VLOOKUP scores cool points! (Note: I have made up the concept of ‘cool points’ to try to illustrate this point, they cannot be found in the VBA editor!)

Let’s arbitrarily assign 7 cool points to loops and 8 cool points to conditional statements. So – when used in concert, how many ‘cool points’ are generated? 15? No!

Many more cool points are generated; in fact, the coolness of VBA techniques combined together is on a higher order of magnitude. To paraphrase the great management scientist Russell Ackoff – a system is not the sum of its parts, it is the product of its interactions. So, the above combination would be worth (7 x 8) 56 cool points. My argument is that things get a lot more exciting as you combine techniques together. Things move to another level. I have seen the reaction again and again in people I work with: ‘Cooooooooool!’

Download the example file, work along yourself, and feel the power of Excel VBA in action. Can you see potential time-saving applications in your work? And, how many ‘cool points’ would you give this application? 🙂

Get on the Tiger mailing list (click left) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

Subscribe To The Tiger Mailing List

Enter Your Details Below…

On the mailing list, you will receive:
– Details of the livestreams in advance
– Information about Tiger Spreadsheet Solutions products including special offers for mailing list subscribers
– Details of new YouTube video releases

Once subscribed, you will receive a confirmation email.  If you do not receive a confirmation email, get in touch via the contact form in the ‘About Us’ tab above.  You can unsubscribe at any time by getting in touch via email, or via the contact form in the ‘About Us’ tab above.

* indicates required



/

( mm / dd )


Posted in Excel Guidance Articles, Excel Guidance Videos & Articles, Uncategorized | Comments closed

What are Excel VBA Variables?

By Chris Mortimer

CLICK HERE to download the EXAMPLE FILE used in the video.

I like to think of an Excel VBA variable as a place to store information. Now, this definition is not particularly exciting! But, thinking about variables in this way can help understand them and maximise their impact in your work.

Excel VBA Message Boxes

Let’s explore this definition a little more. You could compare an Excel VBA variable to a cell in a spreadsheet – which is, of course, where we usually store information in Excel. Indeed, you could substitute a VBA variable for a cell in many Excel VBA routines and still get the job done; but, it would not be as slick or efficient. Nevertheless, ‘a place to store information’, a bit like a cell, is a reasonable conceptual starting point.

How do we typically use Excel vba variables in coding? Let’s look at the example of a loop. A loop is a repeated set of instructions that allows us to get mind-boggling amounts of work done. However, they require careful control (all of us have been stuck in an endless loop at some point!) VBA variables allow us to achieve control in a neat-and-tidy way.

We could, theoretically, reference any object in Excel to control a loop. For example, we could say ‘keep running the loop until the value in cell A1 = 10’. This would work perfectly well, if the value in cell A1 incremented up or down towards 10 as the loop ran. The obvious downside is that we would have to update an object in the spreadsheet (in this case, a cell) which diminishes the efficiency of the code. Further, a spreadsheet cell is usually visible to the user, which may not be desirable.

With this example in mind, what advantages does a variable offer? First, it is quick and easy for Excel to work with, because Excel can change the value that is ‘assigned’ to the variable very efficiently. Remember, a variable is a place to store information; it is faster for Excel to store and recall information using a variable, than to interact with the spreadsheet to change a cell value.

Second, a variable is not usually visible to the user. This means variables are easier to control because, most of the time, they are ‘hidden’. It is not that we don’t trust the user! Rather, variables control processes that should not be easily changeable, such as looping through code. Variables offer superior control to the programmer, which helps create robust applications that do not break.

With these obvious advantages, it intrigues me that people tend to shy away from using VBA variables. Why is this? As is often the case with Excel, it is problem of communication. The language of variables is not easy to understand, so people think ‘they must be for computer programmers only!’ String? Integer? Long? What is the point of these terms, apart from confusing and often deterring the layperson? Let’s dig into it…

In a practical sense, variables help Excel organise its memory – where Excel compiles information to display what we see on the screen. In an efficient application, you could compare Excel’s memory to a beautifully designed wardrobe – it has differently sized spaces for different items, making everything easy to store, and to find. This is the point of variables – they tell Excel the kind of information that is coming; Excel then allocates an appropriately sized space to store it. This is why we ‘declare’ variables (‘Dim counter as integer’ for example), to tell Excel the type of information (‘integer’), and to create a name (‘counter’) to refer to the variable.

What are some common variable types? An Integer variable can store whole numbers up to a value of approximately 32,000 and requires a small amount of memory (just two bytes!) A Long variable can store larger whole numbers but requires twice as much memory. Other variables store decimals (Single, Double), text (String), and other data types. You can find comprehensive lists online. The main variable types I use are Integer, Long, Single (for decimals), String (for text) and Boolean (true or false).

When declaring a variable, we must also name it. A simple letter (Dim ‘x’ as integer) would do the job; however, fans of the channel know that I prefer informative variable names. An informative variable name tells the programmer what the variable does. For example, ‘counter’ is an informative variable name because it counts the number of times a loop has run. I use ‘ChrisCell’ when looping through a collection of cells, because the variable (in this case a ‘range’ variable) represents a cell. We can use any name apart from words that Excel reserves for its own use (‘row’, ‘cell’ etc.) An informative variable name requires more typing but allows us to understand what is going on much better; for me this is a worthwhile trade-off.

So, why not try out some VBA variables? You can find examples of all the above variables in our tutorial videos. They are central to the cool macros we create. Developing basic confidence in their use should lead to slicker and more robust Excel implementations. Good luck and let us know how you get on!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:
https://www.facebook.com/TigerSpreadsheetSolutions

Contact us here.

Posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed