Author Archives: Chris Mortimer

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.

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
Video to follow 24/05/19

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 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

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

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