The Excel INDIRECT function

Why Online Excel Courses Often Fail For Professionals

There are countless resources online for improving your Excel skills: Excel Courses, E-Books, Google, YouTube  and a billion Blogs, it’s overwhelming to say the least. So how do you wade through it all and make some progress? If you are thinking of learning online or perhaps you are not getting very far with books and googling then this article will make you aware of the pitfalls before you invest time and money in improving your Excel skills.

First, ask yourself why you want to get better at Excel

Fundamentally, there needs to be a reason to get better at Excel, you need to think Strategically“Strategy without tactics is the slowest route to victory. Tactics without strategy is the noise before defeat.”― Sun Tzu

Almost every Excel course or Book wants you to get better at Excel (duh, of course).But, almost all of them fail at helping YOU answer the question ‘Why do you want to get better at Excel?’Most people’s response to that question is along the lines of…

• I want to get better at Pivot Tables
• I want to do V-Lookups
• I need VBA skills

Frankly, those are poor reasons and most Excel Courses, trainers or Excel book authors will say…’Sure, I can help you with that’. Now, I help professionals get better at Excel and frankly I find that kind of advice and support to be poor.Since, I have been using Excel for over 10 years in the biggest and best organisations like Investment Banks, I know a thing or two about how Excel is used. So, whenever someone comes to me in the hopes of improving their Excel skills, I challenge their reasons and ask ‘Why?’Then I’ll get a slightly better response like:

• I’m a financial controller so I need to do Pivot Tables
• I’m a PMO Analyst so I need to do V-Lookups
• I’m a Reporting Manager in Banking and I need good VBA skills

Slightly better, but the reasons are still a bit random. When you are just learning random skills, that is very tactical thinking and like Sun Tzu said above, tactics without strategy is the noise before defeat.Excel skills, or should I say, the RIGHT Excel skills learned for the RIGHT reasons did wonders for my career, because they fit in to a wider strategy of career progression. I’ve had plenty of people approach me who I’ve told to not focus on learning Excel because it wasn’t the right move. 

Not all Excel Teachers are created equally

For example, I had a senior Financial Controller with ambitions of becoming a Finance Director in a Bank ask me what Excel skills she should improve. I asked some questions and discovered she had a small team including someone who was good at Excel but she wanted to carry out some of the more sensitive data analysis herself. I suggested that her problem wasn’t a lack of Excel skills but poor delegation skills and that improving her delegation and building trust with her team was going to get her promoted much quicker than devoting time to learning Pivot Tables and Macros!This isn’t advice the average Excel course provider or Excel book author talks about. Why?Because they are Excel specialists.If you are a professional like an Analyst, Controller, Accountant, PMO/Project Manager or Management Consultant then you need solid Excel but do not put your skills, your career in the hands of an Excel specialist who doesn’t get the bigger picture of how Excel fits in with your career.They will have you learning things you do not need and send you down time-wasting rabbit holes. Worse still they won’t tell you what to focus on or how to market your skills.

Pros and cons of learning Excel via Google and YouTube

The biggest issue faced by students is that the internet has given them access to cheap or free information.Let me tell you NOTHING is more costly than cheap or free, especially if you are learning things from the wrong people. But more than that, while there are some good free resources on the internet, I have found that free is highly unstructured, random and often causes confusion and overwhelm. And that’s when it’s good! When it’s not good then it’s a waste of time, it doesn’t get implemented.The single most important factor when learning a subject like Excel is structure and curriculum. The wrong structure and curriculum won’t leave you any better off especially if you are a professional who needs to understand how to actually apply Excel to real scenarios, not just “Kendrick has 10 apples and Drake has 13 oranges” type of problems.If you are very young, don’t have funds but have the time, then use Google to slowly get there. If you are a working professional, take the shortcut, invest in a good course with someone you have access to. Ideally someone who can see where Excel fits in the wider context of your career and learn from them.

The best way to use Google

I’m not anti-Googling and I’m not saying it will prevent you from greatness but I use Excel with Google in a very specific way. For example, if I have to do a complex formula or complex bit of VBA/Macros code it would have taken me 30-40 minutes to work it out. It takes me 5 minutes to Google it and 2 minutes to tweak it so I save time. As you systematically raise your Excel game, your Googling will be more and more effective and you will save more and more time. Here’s a graph to explain this:My philosophy with Excel and work is all about saving time so yes, I use Google but to speed things up and I can do that because I built a foundation of solid Excel skills.       Advanced Excel VBA 

Advanced Excel VBA can be a massive undertaking and most of the people who teach it do a terrible job. I’ve seen Excel VBA courses start teaching you the jargon first, they get you used to the environment. That really is ridiculous, it shows a complete lack of empathy from the trainers. VBA is a life altering skill. I don’t say it lightly that it helped me go from a £27K a year salary to a £100K a year salary. It’s done similar for many my students and colleagues and when it’s mis-taught then that’s a missed opportunity.The other thing that is mis-taught is teaching ‘perfect code’ and solutions to professionals.That’s just plain bad.I’ve had people numerous ‘experts’ over the years tell me that my code was messy and clunky. I completely agreed with them BUT I got the problem solved rapidly, sure I can take my time and build an elegant solution but 9 times out of 10 when that isn’t the focus of your job as an Accountant, Project Manager etc then it’s a poor use of your limited time.

My recommendation for Professionals needing to learn Excel

Having worked in big organisations for over a decade and solving real world problems with Excel, I can tell you that there are three kinds of problems that require Excel to solve and four kinds of Excel solutions:

The three types of Problems are:

1. Tracking or organisation of data.

2. Analysing data whether that’s regular analysis or ad-hoc analysis. Forecast, variance analysis, or for example, you’re working on a project and need to know what milestones are going to deliver in several months’ time.

3. Communication of data, i.e. reports and MI.

And those three problems only need four kinds of solutions to tackle them:

Four kinds of Solutions

1. You are going to find the information. That means you are going to look it up or find it.

2. You are going to treat that information with rules i.e. you find some information, do some analysis saying, ‘I want this data to do these calculations or I am just looking for this, if it looks like this then I want that.’ Those are very important things the if and the then.

3. Deal with the non-numerical information that most of us analysts, accountants, controllers, PMO’s, and management consultants are often dealing with i.e. names or product codes.

4. Summarising your data after your analysis i.e. a table, chart or graph. i.e. the end game

This framework is a more strategic view of how to use Excel, rather than just jumping to ‘should I use VLOOKUPS, Pivot Tables? etc’. This approach allows to approach Excel in a wholesale manner than piecemeal (here and there, where you learn-forget-learn-forget).

 Conclusion: Excel should be thought of as stepping stones to greater things…

What I benefited from most and consequently teach my own students is that Excel presents a very unique opportunity to:

SAVE TIME

This should be the goal for a professional who works with Excel on a daily basis. So, you want to learn Excel, then learn at least some VBA which will help you save time, then repurpose that time to adding value in your organisation, impress the right Execs and open doors up.If you would like to sign up for one of my Excel courses then you can learn more here:

The post Why Online Excel Courses Often Fail For Professionals appeared first on Earn and Excel.

Dollar $ in Excel – Absolute and Relative References

Excel’s default is to use relative reference when using formulas. That means that when a formula is copied and pasted into another cell the formula will change its reference points by the exact number of columns and rows that you moved the formula.

We achieve this by including or omitting the dollar sign.

Demonstrated below in the left worksheet we entered the formula B1*A2 and in the right worksheet we copied that formula down into C3 but Excel changed the formula to B2*A3 (One row down the column, just like we moved the formula)

How do we stop this change from occurring? Using absolute reference and learning where to insert a dollar sign ($) we can tell Excel exactly what elements of the formula we want to stay the same as we move from cell to cell. Cell referencing is about linking one cell based on the current cell. It is mostly known for altering formulas. For example, the below worksheet shows =B$1*A2 entered into C2, thus, linking B1 to C2. If B1 is altered in any way C2 will also be altered.

By adding the dollar sign into the formula you can tell Excel to keep the row the same, keep the column the same or keep the row and the column the same.

Keeping the Row the same:

Enter the dollar sign before the Row Number in the equation to keep the row the same. In this instance, we want to keep B1 as the cell that is multiplied by all other cells. We enter =B$1 to show this as we move down column D. If you were to move this formula to Column E the reference will stay in Row 1 but it will also move to Column C because we did not make Column B an absolute reference. The same is true for the second half of the equation instead of Column A being the reference it would now be Column B.

Keeping the Column the same:

Enter the dollar sign before the Column Heading in the equation to keep the column the same. Here we added a few more numbers to row B and now want to multiply down the column. We enter $B to tell Excel this.

Keeping both the Row and the Column the same:

Combine the above two placements of the dollar sign to keep both the column and the row the same in the formula.

Enter $B$1 to tell Excel to use the exact Column and Row as in the original formula. No matter where in the worksheet you copy the formula the B1 will be used as the multiplier, however, the second value (A2) will change because it is a relative reference.

Tip: When typing your formula, you can hold F4 and toggle from relative reference to any of the three styles of absolute reference listed above. For Example, enter =B1 and hit F4 it changes to =$B$1, hit F4 again and it changes to =B$1, hit it again to get =$B1, and one last time to return to =B1

Using Absolute Reference

In the example worksheet below, the company bases its future advertising costs on last year’s sales. Twenty-percent of the previous year’s sales will be allocated to the new year’s advertising costs. We are going to sum each district’s sales for the year by making the row range an absolute reference. Then sum each district’s sales for the month making the column range an absolute reference.

Finally, setting both the column and row as an absolute range we will the total sales per month by the 20% Advertising costs.

Total District Sales for the Year:

1. In Row 14, Column B enter =SUM(B$2:B$13) to add all values in Column B.
2. Copy across Row 14 and Excel will keep the Row Range (2 -13) the same but will change the columns.

Total Sales Per Month

1. In Cell E2 enter =SUM($B2:$D2) to add the values across the Row 2.
2. Copy down Column E and Excel will keep the Column Range the same (B-D) but will move down each row.

Calculate Advertising Costs per Month

1. Enter the 20% into any cell (this example it’s entered into B15)
2. We will use the data from Column E since it is a sum of the three columns before it.
3. In F2 Enter =E2*$B$15 telling Excel that as me move down Colum F we want to multiply each row by the data in B15
4. Double-clicked the bottom right of the cell and Excel will auto-fill the remaining pertinent cells within Column F.

Now we have a total for each month’s projected Advertising Costs. Copy the formula from District Sales for the Year over to E14 and we now have the Total Projected Advertising Costs for the next Year

Using Absolute Reference with Multiple Worksheets

In the above worksheet (Sheet 9), we have the 2018 Projected Costs for each month. We want to use our original worksheet (Sheet 8) where we projected the advertising costs to fill in Column F for Advertising.

1. In Cell F2 of Sheet 9 above enter the equal sign (=)
2. Either Click in Sheet 8’s Tab and select January’s Advertising Costs (F2) or type Sheet8! Followed by $F2 or =Sheet8!$F2
3. Either double-click the bottom right corner of Cell F2, click and drag down the column to auto-fill the Projected Advertising costs per month or copy the formula and paste into each cell.

Changing the referencing of a formula to absolute is quite simple as you have seen. The key is to know exactly how you want your formulas to work. If you know that you want a range of data to be multiplied by a single cell then you know to use absolute referencing, which, is a lot less time consuming than creating a column next to your range and adding the multiplier down that column and working across the rows to get your answer. Absolute referencing will make your worksheets look much cleaner and well-organized.

Before you go, if you want to learn more about how Excel References work then feel free to check out our Formulas Book 'The only 27 Formulas a Professional Needs to Succeed'

The post Dollar $ in Excel – Absolute and Relative References appeared first on Earn and Excel.

How to Alphabetize in Excel 2010

How to Use Conditional Formatting in Microsoft Excel 2016