Author Archives: Chris Mortimer

How to Change Grades to Numbers in Excel

How to quickly convert grades to numbers in order to measure student progress in Excel?

How can you change grades to numbers in Excel? It’s something many teachers out there are trying to do in order to measure student progress. And it’s something I have been asked to do on projects numerous times! Learn how to do it, and avoid the need to hire somebody like me. Welcome to Never Hire Me.

Click here to download the STARTER Excel file for this video.

Click here to download the COMPLETED Excel file for this video.

Excel cannot easily understand the difference between two letters. So, the best option is to convert the letters to numbers, and then to work out the difference between the numbers. The concept is simple enough, but how to actually do it in Excel?

First, we build a table to assign a value to a particular grade. It is sensible to position the table on another sheet for clarity; but, referencing other sheets in formulae can be tricky. So, we use a ‘named range’. Using a named range makes formula referencing much easier later – Chris shows you how to quickly set one up in the video.

Match a number to a range in Excel thumb

With the first step complete, we return to the original sheet and implement a VLOOKUP formula. The formula ‘looks up’ a ‘lookup value’ (ie. the student grade) in a table, and returns a value from another column in the table that is on the same row. We work through the formula in the video, step-by-step – make sure you follow along with Chris and try to build it yourself.

The final step is to copy the formula across to the second grades column, and to implement a simple arithmetic formula to establish the difference between the grades. And that’s it! You have combined VLOOKUP with a table and a simple arithmetic formula to find the difference between two student grades.

Did you get the difference between two letters in Excel? Let Chris know in the YouTube video comments, he will get back to you.

———————————

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

How To Match A Number To A Range in Excel

What is the best way to match a number to a range in Excel?

How can you match a number to a range in Excel? It’s a common reason I get hired! Learn how to do it, and avoid the need to ask the IT department, or to hire somebody like me.

Click here to download the Excel file for this video.

Thank you to channel viewer Phillip for sending in this example. Phillip is recording results of physical tests for the military which involves awarding scores for different exercises, including press ups. The scoring system allocates different points for different age groups; so, our spreadsheet must match an age to an age group. Difficult! So many people find themselves trying to do this, and do not know the techniques required.

Match a number to a range in Excel thumb

Phillip’s setup is typical, the age ranges are defined in a cell using upper and lower bounds, and a hyphen, eg. ’17-21′. The problem is that Excel simply cannot make sense of ranges articulated in this way. So, the first step is to express these ranges in a way that Excel understands; this means extracting the lower bounds and listing them in a row.

With this set up, we can think about applying formulae to get the job done. In the video, Chris covers two possible approaches, one using HLOOKUP and the other using MATCH. Both applications are non-typical: HLOOKUP uses approximate match as its 4th component, for example, and MATCH uses ‘less than’ as opposed to ‘exact match’ in its 3rd. Go through the video and work along with Chris to learn how to apply these interesting and useful applications yourself. You will be matching values to ranges in Excel in no time!

Did you manage to match a number to a range in Excel? Let Chris know in the YouTube video comments, he will get back to you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

How To Use Excel VBA Code – Real Example

How To Use Excel VBA Code To Speed Up Manual Work – Real-World Example

Click here to download the STARTER Excel file for this series.

How can we use Excel VBA code to speed up manual work in Excel? In this series we explore an example Excel VBA project sent in by Eric, a viewer of the channel. We aim to build an Excel VBA application to move data around a file at the ‘click-of-a-button’, saving hours of frustrating manual work. The application should be flexible and scaleable enough to handle changes in business circumstances – a challenge indeed! Let’s get into it…

How To Use Excel VBA Thumb

Part 1 – Planning and Looping Through Worksheets

In part 1, we consider the task at hand and complete a simple task that moves us in the right direction. This may seem to lack ambition, but it is important to plan any VBA task and to move forward in easy steps; if you simply jump into the VBA editor, you are likely to get into trouble. We have all been in a coding tangle before!

We know we have to take data from four sheets in the file. So, a sensible first step would be to set up a mechanism to loop through sheets in the file. It’s simple enough, and it sets us off in the right direction, and allows us (and our client?) to build trust and confidence. In the video, Chris uses a particular sheet referencing technique (using sheet index numbers) that allows us to quickly exclude sheets from the loop – since some of the sheets in the file do not contain data. We also look at how to set up the loop to cater for additional sheets in the file – remember our solution has to be flexible and scaleable.

With this relatively simple task complete, we are on our way! See you in part 2. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 2 – Dynamic Range Referencing and With … End with

In part 2, we create a mechanism to loop through the data on each worksheet. Looping through ‘static’ data is simple enough – we have done that in other videos on the channel – but Eric’s briefing presents an additional challenge: how to loop through a variable amount of data?

What is meant by ‘variable’ in this case? As you can see in the download file, each sheet contains a different number of rows; further, Eric wishes to be able to add new sheets with any number of rows. What is required is a solution that can handle these different situations without the need for additional coding; what is needed is a *dynamic* solution.

The video explores how to create such a solution. We learn about the powerful .end(xldown) construct which selects to the end of a dataset and drives the dynamic mechanism. We use the ‘step into’ facility in the VBA editor to work through the code whilst looking at the Excel window to understand how it works, and to test the new mechanism.

Finally, we look at how to integrate With …. End With to simplify the code and to avoid having to select sheets. Selecting objects such as sheets and cells is a ‘direct referencing’ approach; routines built in this way can take some time to execute, and there is a more efficient approach.

We would much rather have a fast routine built on ‘remote’ referencing; ie. a routine that does not select sheets but uses sophisticated referencing to get the job done faster.

Part 3 – Loop within Loop and Countif

In the third video, we first implement a ‘loop within a loop’ – the powerful mechanism that allows us to get the first part of this job done. Conceptual thinking always comes first, so what exactly are we trying to do, and can we explain it clearly in normal language? Let’s try…

We already have one loop set up which loops through the worksheets in the file. The next loop, which operates ‘one level down’ and is embedded within the first, will loop through the name cells on each sheet. With the conceptual understanding clear, we can move onto the programming.

Chris uses a ‘for each object in the collection’ / ‘for – each’ loop to work through the name cells on each sheet. This type of loop could be applied to open workbooks, shapes on a sheet, sheets in a file, or another of Excel’s ‘collections’ of objects. It shows how, if we can navigate Excel’s object hierarchy (simply the ‘architecture’ of Excel), we can make coding easier.

In this case we loop through each cell in the range of cells that contains customer names, as defined by the dynamic range reference we created in the previous video. We test the mechanism using message boxes to extract information from the VBA editor during code execution. Job done – next!

The next step is to understand if a particular name appears in the list of names we are assembling. Remember, Eric has requested a list of ‘unique’ names, so names must not be duplicated. So, how can we check if a name already appears in the list? We set up a thing of beauty: worksheet formulae working together with Excel VBA to get a job done. In this case we create a ‘COUNTIF’ formula to establish if a value appears in a range; if the formula returns a value of zero, we know the name needs to be added to the list.

Onwards!

See you in part 4. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 4 – Importance of Frequent Testing and a Conditional Statement

In part 4, we discuss the importance of frequent testing of computer code, and test what we have done so far to build confidence in its accuracy and robustness. It might appear that Chris is obsessive about testing … and you might have a point! But, this mindset is the result of hours spent working with Excel VBA.

The truth is that frequent, small-scale testing eliminates the need for large-scale, stress-inducing testing later in the process. Testing should imbue the development process, rather than being bolted on at the end. We again use a message box and the ‘step into’ facility to work through the code and understand what is going on. Don’t forget to view the VBA editor and Excel worksheet side-by-side, if possible; an additional screen can really help if you have access to one.

With the testing done, it’s time to transfer data from the four worksheets into the mechanism on the Analysis sheet. This is fairly routine (only because we have already done the hard work!) but we wish to do more; we wish to tell Excel to do something if the name appears in the list we are compiling. We wish to tell Excel to do something if a condition is met.

With this in mind, we implement a simple conditional statement, followed by an If-Then-Else statement to direct the code in one of two directions. With this mechanism in place, we are almost ready to assembly our list of unique entries … all at the click of a button.

See you in part 5. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 5 – Combining A Worksheet Formula with VBA Code

Some more (yes, more!) testing is required at the beginning of part 5 to ensure the file is working as we expect. With a quick test completed, we set about creating a mechanism to ‘stack up’ entries in a list. This mechanism moves us towards our initial goal of a list of unique entries in the file.

Regular viewers of the channel will be familiar with the components: first, we use COUNTA to count the number of entries currently in the dataset. Then, we add the .offset method in the VBA editor; .offset allows us to move away from an ‘anchor point’ – the top-left corner of the dataset – by a certain number of rows and columns (anchor.offset(x,y).

By pointing Excel to the cell in the workbook that contains the COUNTA formula, we can make magic happen: a powerful interaction between .offset and COUNTA creates dynamic position control which allows us to create a list of unique entries at the click-of-a-button. A powerful mechanism indeed – check it out in the video!

As the thrill of this instant functionality subsides, we set about testing the accuracy of the new mechanism. Chris implements three (yes, three!) different tests to establish its accuracy, including some manual checking and an IF formula to check our dataset against a pre-prepared one. Only then are we ready to move onto the next step.

Hopefully you are understanding the importance of a methodical approach in Excel VBA coding, characterised by small steps and almost constant testing. This is the approach Chris applies in his actual Excel VBA development work – we hope it helps you shape your own practice.

See you in part 6. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

Excel Indirect Function Tutorial

Welcome to our Excel Indirect Function Tutorial!

BY CHRIS MORTIMER

Welcome to Tiger’s Excel Indirect function tutorial. The Indirect formula has super powers that could be a game-changer for you. This example is inspired by a recent work situation where the formula saved me a huge amount of time.

Click here to download the START file for the Excel Indirect Function Tutorial.
Click here to download the COMPLETED file for the Excel Indirect Function Tutorial.

How could we summarise Indirect’s ‘magical powers’. Well, let’s suppose you wish to apply a formula to multiple sheets. In my example, I wish to see how many times data appears on datasets from different US states. We can use Countif to do this, as Chris demonstrates in the video. And, voila! The analysis is complete.

Excel Indirect Function Tutorial Thumb

So, what’s the problem? The problem is that we need to apply the same analysis to more than one sheet. In the example we have four sheets, but you might have fifty, or more. Can you imagine building the formula fifty (fifty!) times, in order to apply it to different sheets. This would be intensely frustrating!

The fact is that there are too many people out there trying to do this kind of manual task in Excel. I wish a few more of them watch the Tiger channel 🙂

This is where Indirect changes the game. Indirect converts the contents of a cell into a text string and allows us to use the text string in a formula. Such a technical exposition does not sound exciting; so, how else can we express it? How about ‘Indirect allows you to input sheet names into column headers, and to directly reference the sheet names in formulae’?

Perhaps this sounds more exciting. No matter, Chris demonstrates how the formula works in the video. Correctly implemented (the formula is notoriously tricky to work with), Indirect allows us to control the sheet that a formula looks at by simply changing the value in a cell. Crazy! And incredibly cool.

The implications are significant. I managed to speed up a task that would have taken most an afternoon, and complete it in just a few minutes.

Did you manage to complete the task in the video? What have you managed to accomplish using the Indirect formula? We hope the tutorial was helpful for you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

Excel Data Table Tutorial

Welcome to our Excel Data Table Tutorial!

Welcome to Tiger’s Excel Data Table Tutorial Series – the only Excel videos you will need to get you up to speed with this powerful – and often misunderstood – technique.

Click here to download the start file for Video 1.
Click here to download the completed file for Video 1.
Click here to download the completed file for Video 2.

What is a data table? Like so many things in Excel, its name does not help us understand its role. An Excel data table does much more than store ‘data’; it actually presents outputs from an Excel model. Applied correctly, it allows us to instantly test any number of model inputs, and identify the input(s) that create(s) the best, or ‘optimal’ output. It’s quick and powerful ‘what-if’ – analysis: what would the output be if this were the input value? So, I suggest it would be more accurately called an ‘Outputs’ table, or even an ‘Optimisation’ table.

Excel VBA Move Data Thumb

That’s exactly what an Excel data table allows us to do – quickly optimise a model. There is an important point to note here, we certainly need a ‘model’ to use a data table! A model consists of an input cell (or cells) linked to some calculations and an output. The 1st video features what Chris calls ‘the simplest possible model’; but, it is still a model because it consists of input, process and output (eg. 4 x 3 = 12).

Simply put, a data table automates the process of finding the best output from lots of inputs. It’s something we could do manually – inputting values into the model and noting down the outputs in Excel or somewhere else as we move towards the optimal solution. A data table does this for us at the click of a button, instantly tabulating outputs for any number of ‘candidate values’ that we choose ourselves.

EXCEL DATA TABLE TUTORIAL VIDEO 1 – CONCEPT AND SIMPLE EXAMPLE

As we see in the video, we can use a one-way data table to test a single input, or a two-way data table to test two inputs. You may also hear the terms ‘one-variable data table’ or ‘two-variable data table’ used. Both can be tricky to set up: a particular layout is required, the table must be properly linked to the model’s output cell, and we must understand the meaning of ‘row’ and ‘column input’ in the data table dialogue box (it’s confusing!)

Further, data tables are difficult to tweak / delete, you might be familiar with the ‘you cannot change part of a data table’ error message, for example. We deal with how to quickly delete a data table in the second video in the series.

Armed with this simple foundational example, you are ready to move onto video two where we explore a powerful real-life application of an Excel data table.

EXCEL DATA TABLE TUTORIAL VIDEO 2 – REAL-LIFE APPLICATION

First, thank you to channel viewer Milan for sending this one in. Milan is working in the garage door industry and wishes to find the best combination of two panels (of different lengths) for a specified door height. Without data tables, this kind of problem could take hours to solve!

In the video, Chris takes you through the model he built to give Milan an instant optimal answer for any garage door height. The secret: the power of data tables.

We know from part 1 that we cannot use data tables without a ‘model’. A model consists of input, process and output. In this case, inputs are a combination of the two panels, and the output is the garage door height that this combination creates. Simple arithmetic formulae comprise the ‘process’ because they do the working out. It’s a simple Excel model – they do not have to be complex!

Then, Chris goes through the setting up of the data table, emphasising the need to be steady and systematic in your approach. We also deal with the tricky issue of editing and deleting data tables; once implemented, they never seem to go away! Chris demonstrates how to do it.

With the two variable data table in place, it is possible to ‘optimise’ by simply looking at the table and doing some simple workings manually. But, we want more than that: we want a fully automated solution that makes the best combination absolutely clear.

To do so, we implement more formulae (=abs(), =min() and =match()) to identify the specific combination of panels that provide the best door height. Moreover, we display the combination at the top of the spreadsheet (everybody hates scrolling down to find important information), and even include useful information like excess height.

It’s an illustrative example of a simple and powerful Excel model, with data tables deployed to powerful effect.

So, did you manage to get the data table working? Can you feel the power of this awesome optimisation technique? Have you found an application in your work? Leave a comment on the YouTube video, Chris will get back to you.

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

Excel VBA to Move Data Around a File – My Approach

A super powerful approach to use Excel VBA to move data around a file

Click here to download the Excel file for this series.

The super powerful Excel VBA technique I use to quickly and flexibly move data between Excel worksheets.

Moving data around an Excel file? It’s a topic we’ve covered before. And it’s worthy of further attention because so many people out there are trying to do it. This kind of manual, repetitive task takes time and can frustrate!

Excel VBA Move Data Thumb

First, I recommend checking out our Excel VBA for Post-Beginners series where we apply a basic approach to move data from a database to a report template.

This approach certainly works and is a great starting point for beginners. But what are the drawbacks? Well, if we wanted to tweak the code (for example, to add another piece of data to the report), we would have to access the VBA Editor to update the macros. This is not ideal – it is unlikely a typical client would be confident amending the process in this way. Is there a better way?

The approach I teach in this video series offers superior speed, flexibility and robustness. It is the approach I actually use on my Excel VBA projects, and it allows both programmer and customer to quickly update the report if required, without going into VBA. Sounds cool, right? Let’s get into it.

Excel VBA to Move Data Around – TUTORIAL VIDEO 1

In the first video, I explain the mechanism, and begin to set up a new mechanism from scratch, for demonstration purposes. Yes, it takes some time to set up (we actually do not do any exciting coding in this video), but the rewards are significant. The first step is to create a table on a supporting sheet in the file; the table displays information about where we want the data to go (destination) and where the data is located initially (origin). Sheet names and cells are required – you can simply type them in.

Yes, I am asking for some viewer patience here! But, this rather routine task creates magic later on. VBA will actually interact with this table to move data round as we require. This means that building up the process is as a simple as adding new lines to the table.

Further, any tweaks required later require just a simple adjustment to the table data. This is why programmers and clients alike should love this approach: after the initial code setup (we cover that in the next video) it facilitates powerful report configuration without having to open the VBA editor.

(If you really cannot wait for the next video, all the code is included in the download file – see if you can work it out for yourself!)

Excel VBA to Move Data Around – TUTORIAL VIDEO 2

In the second video, we begin to set up the short but powerful routine that makes the magic happen. It’s a concise macro with some tricky lines of code; that’s why it’s important to be steady and systematic in your approach, to avoid getting in a coding mess! (We’ve all been there…)

Crucial to the mechanism is a dynamic selection – a selection that expands as new rows of data are added. The first part of the video deals with this ‘dynamic’ code using an established Tiger favourite – Range(range,range). This construct, combined with .end(xlDown), creates the functionality we need.

Next we test the routine we have created. Yes, it’s tempting to simply plough on with the coding, but that might create problems later. We take the time to test the code, using a message box to flash up the cell value; we even add new rows of data to prove the routine is ‘dynamic’. Cool!

Excel VBA to Move Data Around – TUTORIAL VIDEO 3

In the third video in the series, we complete the mechanism and ‘play’ with it to understand its unique power. Before we get there, however, there is some intricate coding work to do. With this kind of complex syntax that interacts with objects in the file, the likelihood of errors (and stress!) is high. Therefore, or first step is to implement a simple line of code that ‘does a similar thing’, as an intermediary step. When faced with difficult coding always simplify, test, then build up.

With the simpler line of code in place, we can substitute in the more powerful constructs. In this case we replace normal references for the variable name; using a variable in this way can feel unnatural – but remember that this is a range variable, so it stores a cell address. That means we can use it to reference cells in the file. Cool! Chris incorporates this new element and tests. Finally, we ‘recycle’ a line of code that we know is functioning to complete the syntax; some adjustments to the offset method to get Excel looking at the correct cells completes the process.

What we have created is supremely powerful and flexible. No more editing VBA when we need to add a new item to the report; rather, we can simply add a new line of code to the backend table. Sweet!

Excel VBA to Move Data Around a File – Series Release Dates
Video 1 – 03/05/19 – https://youtu.be/XStFwJW-oCQ
Video 2 – 17/05/19 – https://youtu.be/v2FZcYcOSwA
Video 3 – 31/05/19 – https://youtu.be/156KqQZ-pgA

What did you make of the Excel VBA to Move Data Around video series? Have you used the approach yourself? Leave a comment on the YouTube channel and let’s start a conversation!

FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive 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

Excel Spreadsheet Projects: Your Complete Guide

Here is your complete guide to Excel spreadsheet projects!

Click here to download PDF file from the video.

Contact us here.

Posted in Uncategorized | Comments closed

Your Weird Skillset is Your Best Asset

Let’s talk about unconventional career paths and how they can create magic!

Weird Skillset Thumb

If you haven’t taken a conventional career path you might feel frustrated from time to time. Are things really coming together? Are all these things I have learned actually useful? Am I a jack of all trades and master of none?

Such feelings can be particularly intense in social situations, around people with ‘normal’ jobs and those who are conventionally ‘successful’. They appear to take an interest in your funky project; but you feel they are just being polite. I personally dread the question ‘What do you do?’ It makes me shudder!

Though it’s still there, the social anxiety is less acute these days. Over the years I have come to realise that skills and experiences that seem varied, disparate or completely unrelated can synergise to give you unique competitive advantage. This synergy emerges over time and it is difficult to feel it happening. But it definitely happens, in the background, and can put you in a powerful position career-wise.

You might have watched Steve Jobs’ famous Stanford Commencement address. If not, look it up on YouTube. In the video, Jobs talks about his career and how he got his business started. One part in particular resonated with me.

Apple pioneered a unique and beautiful typography in its early systems. It’s one of the features that contributed to a long tradition of elegant design. The typeface created a point of differentiation between Apple and its competitors (until they copied it!)

Jobs is such an interesting character. I was fascinated to find out he took a caligraphy class in College, which is why he integrated the new lettering into Apple’s systems. Without the class, the typeface might not have been conceived, and Apple may never have carved out its market niche for premium products – who knows?

Why on earth was Jobs, a computer programmer, in a calligraphy class? It turns out Jobs got bored of his college programme and withdrew, but not entirely; he remained on campus and picked and chose the classes the interested him, following his instincts, interests and passions.

His aim was self-actualisation rather than anything more practical, so it is funny to think that he eventually helped Apple become the world’s most valuable company. He went about developing an unconventional and varied skillset, a weird skillset … that turned out to be his greatest asset.

Coming back to my humble career, I can say that things certainly got easier once I started the YouTube channel – it creates interest in the company, some enquiries about projects, and provides a very modest income stream. It’s now at the centre of the company’s long-term strategy. But, not everybody can be successful on YouTube (I myself have achieved very minor success); it requires a unique set of skills.

In the feedback I receive on the channel, people say that I speak clearly and that they can understand. I learned how to speak slowly and clearly, and in a manner that people from other countries can understand, through teaching in a high school in northern Japan for two years. In the context of the YouTube videos, it is something that makes the channel distinctive, at least to some extent.

I could not have started the channel if I had not learned VBA programming on the MSc I undertook after returning from Japan. Programming requires attention to detail; I was able to foster real precision in my thinking through studying Japanese for four years when I was resident there.

It’s funny that by teaching English and learning Japanese in Japan I was actually crafting a skillset that would allow me to run a YouTube channel about computer programming years later! It’s impossible to tell how your apparently disparate skills will synergise, and how your unique skillset will interact with the demands of the economy (who would have predicted a business model like YouTube 20 years ago?)

I like to think that somewhere deep in our subconscious we are guided by a force that sees how everything might fit together in the future. This is why, in my view, it’s a good idea to put the conventional career on the back burner, periodically and if circumstances permit, to pursue a hobby, interest or passion that you love, if only as a side project. Things might make sense later on – and you might end up with your dream job, or make a massive contribution to society with something unique (or both!)

Coming back from Japan I felt a little bit lost career-wise. I distinctly remember a couple of social situations where my insecurities were definitely exposed and I responded defensively to people about not wanting to do something conventional. I do regret this. I could not feel everything coming together; but, in the background it was, it’s just the circumstances were not right for it to bear fruit – but it did eventually and I am extremely grateful for it. A weird skillset turned out to be the best asset.

What did you make of this article? 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 Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized | Comments closed

.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 … 🙂

TUTORIAL VIDEO 3

So far in the .cells series, we have dealt with how to work with a single cell selection. No doubt this is useful (and fun!), but selecting ranges consisting of multiple cells presents new possibilities: it might allow us to work with datasets that change in size, for example – a common requirement in Excel projects. So, how would we select a range of cells using the .cells technique?

The key syntax is Range(Cells(a,b),Cells(a,b)) where the first cells construct represents the top-left cell of the range, and the second the bottom-right cell. This incredibly powerful construct allows us to do so much – but is very tricky to code. If you try to write it out without a steady and systematic approach, you are likely to run into trouble and get frustrated! Chris has been there so many times… 🙁

To avoid this, Chris builds up the code step-by-step, starting with a conventional range reference in VBA (eg. range(“A1:A10”)) that you are probably familiar with. After testing this, we can substitute in the more complicated syntax, once piece at a time, testing at each step, whilst steadily building up the final construct. Chris demonstrates in the video – work along with him, and don’t forget to do your own experimentation! ‘Play’ is key.

Quotable:
“Whenever I deal with complicated syntax, I simplify it; write something simpler first, then build it up.”

In the final video, we will aim to substitute the hard-coded values in the VBA editor for cell references. Can you imagine the kind of functionality that might result? The possibilities are mind-boggling. See you in the final video!

TUTORIAL VIDEO 4

Hopefully you are getting to grips with the Excel VBA .cells technique. You’ve learned the basics of one of VBA’s most powerful constructs. But, we’re not done yet!

We have previously used the syntax Range(Cells(a,b),Cells(a,b)) to select a range of cells. Though fun to play with, this in itself is not particularly useful. The reason? We are using ‘hard-coded’ values in the VBA editor. What is meant by this?

Put simply, any value in the VBA editor is ‘hard-coded’. This means that it cannot be changed without accessing the VBA editor and making manual tweaks. As such, it is hardly a satisfactory solution – can you imagine having to access the VBA editor to change a spreadsheet input, every time? It would be very onerous; we certainly cannot ask our clients to do this (because they think macros are magic!)

Rather than inputting ‘hard-coded’ values in VBA, we can point VBA to a cell in the spreadsheet and ask it to take a value from there. This is a profound capability that creates powerful functionality, as Chris tries to explain the video. In the video, we substitute the numbers in the VBA for cell references, and then adjust the cell references by changing values in the spreadsheet. Cool! When we run the macro, the selection is determined by the values in the cells we choose…

We could take things still further. We could point VBA to a cell in the spreadsheet that contains a COUNTA formula, for example. This kind of set up would allow us to dynamically define and update a range – the size of a database, for example. All using the .cells technique.

We hope you enjoyed the four Excel VBA .cells tutorial videos. Let us know what applications you have found, and what other Excel VBA tutorials you would like to see on the YouTube channel.

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 – https://youtu.be/oW2ZqqArIhA
Video 4 – 19/04/19 – https://youtu.be/rf8rK7rzoU0

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