My name is Marcus Small. I am a financial modeller and VBA developer with an ANU Masters degree in Finance. Here you can find An Excel dashboard, VBA and formula page, taking on interesting Excel problems. There are hundreds of Excel templates and examples on the site.
The following Excel dashboard is a trending dashboard which highlights some key organisational metrics by city. As the data is displayed by city I thought I might present the data with some infographics around the cityscape relevant to each city.
The Excel model contains both the raw data and calculations for each of the cities the organisation operates within. It was an enjoyable model to put together.
Some of the areas covered in this Excel dashboard include
Return on Assets (ROA)
Return on Investment (ROI)
The model is mostly a trending model with less detail than the larger Excel dashboard models on the site.
The image above gives a closer look at some of the details and shows more of the trends. Ideally the data would have numbers but that is easy enough to add. This was a bit of fun and looks the part in my opinion of course.
The model is spun on a monthly combo box and will concertina based on the month chosen. This gives the dashboard the ability to go forward or backwards and have the charts snap to the month shown in the combo box.
The trim function in Excel is a formula to take out leading and training spaces. It usually works amazingly well, you simply point the formula at the cell with the issue and it will produce a clean piece of text without any leading or training spaces. This article will focuses on what to do when the TRIM formula does not work as expected. It looks at alternatives to solve the problem.
Recently I was trying to cleanse a data set of trailing spaces. I knew they were there so I went to the TRIM formula. I was shocked to find it did not work. So looking into the matter it seems others have had the same problem. If the text was in A2 my formula looked like this.
However the formula failed. So I used the following
The above formula worked like a charm. The Excel CLEAN function looks at a text string and returns text that has been made clean of line breaks and any other non-printable characters
So when trim doesn’t work consider using the it in conjunction with the CLEAN formula.
The Excel VLOOKUP which is short for vertical lookup, would have to be one of the most used Excel functions. VLOOKUP is a function which allows you to lookup unique data. The premise is to look for a unique identifier in the first column and return data in the same row in another column.
The VLOOKUP syntax has four arguments.
=VLOOKUP (Lookup Value, Range, No Cols Offset, True/False)
Reference - the cell or text or value you are looking.
Range- the range that reference is in contained within. The Reference must be contained in the very first column as the data is looked up from left to right.
No Cols Offset - from the reference column
True/False -This is the type of match - either exact or approximate.
Key Point - VLOOKUP only looks to the right!
VLOOKUP has two arguments for matching data, exact match and approximate match. The choice for either of the two is made with the 4th argument. It is True False above but Excel calls this [Range Lookup]. We put a False (or zero) to make Excel get an exact match or True (or One) for an approximate match.
Example 1 - The VLOOKUP Exact Match
Probably the most well known and used method for VLOOKUP is the exact match. This type of match is ideal especially when you are looking up a unique key and want to return data based on that key.
Once you have identified your unique identifier you can look up any column to the RIGHT of the column you are searching for.
The column Offset is 6 columns remembering to include the first column in your count.
The syntax to a lookup for the above would look like the following.
Where H2 is the Lookup value 3034292, the Range Lookup is A2:F10, the column offset is 6 and the type of lookup is an Exact lookup 0 (this can also be FALSE but why type 5 characters when 1 will do).
To return the year for the transaction the only thing we would change would be the Column reference. This would be 5.
So the VLOOKUP would work on any of the columns to the right of the first instance of the saleskey above. It is important to remember with VLOOKUP that if your lookup key is not unique the first instance in the list starting from the top will be returned. The other instances will not be considered.
Example 2 - The VLOOKUP Approximate Match
The approximate match with VLOOKUP is not used as commonly however is exceptionally useful. It provides the opportunity to group data and search within bands for a particular result. One such example is grades in school, where grades are given inside certain scores. From 60-75 might be considered a C grade.
The formula is C2 is as follows.
Where B2 is the score, E2:F6 is the range of the tables with the banded grades, 2 is the column to offset by and 1 (or True) for the last argument which is for an approximate match.
Excel now has moveable changeable 3D image library just for you!!! If you are using the subscription version of Excel you now have access to 3D shapes. and best of all you can move them to the angle you wish them to be in. The library is quite extensive with images grouped into categories.
To access the shapes click on the Insert Menu.
On the Insert Menu choose 3D models – from Online Source. This will give you access to Microsoft’s online 3D library. The following is what the file looks like with a 3D spinning option.
The image can be moved in any direction, the spinning tool above is very intuitive and easy to use.
Here are the results from some playing with the above image.
You can see how the images are different. There is a large range of images in the library.
From an infographics perspective I wish this feature was available when I was building the infographics section of this website. There is so much awesome content. Have a play around with them. It is a lot of fun!!!
Traditionally I have used the OFFSET function for dynamic ranges within Excel. It is solid and gets the job done on a given range. One thing to remember when using OFFSET is to ensure you include a big enough row count for vertical ranges. It works really nicely on a tabular data sets. If you are unfamiliar the following is an example of a dynamic range with a AVERAGE formula:
However, there is another method for trapping dynamic ranges, let’s say we want the AVERAGE for the cells in column B and the likelihood that column B will grow is extreme. The same formula can be written as follows:
While the formula is longer (by 4 characters) it has the added advantage of being a non volatile formula. If there are too many Volatile formula in a spreadsheet it can start to chug a bit.
Breaking the formula down cell B2 is where the formula range starts, the INDEX formula comes to the party where it creates the second cell reference in Column B. It looks at the range between B2 and B90 and is looking for a specific Row number in the first instance. Enter the COUNTA formula which acts as the solution for which Row number. Counta counts all of the cells that have data in a given range. Blanks are not included. So if the data stops in A15 then all of the data from B2:B15 are included in the AVERAGE. It is a master stroke and keeps things non volatile.
Try it with your own data. Here is a workbook that displays both techniques.
I often get asked what do I need to know to have advanced Excel skills. These days the ability to analyse data in a multitude of ways requires Excel skills that are better than asking Google for assistance every step of the way – you need to know formulas, charting, conditional formatting, tables, pivot tables including Power Pivot, excel tools such as goal seek and solver, using short cut keys and finally knowledge and use of Visual Basic for Applications.
Advanced Formula Techniques
Formulas are the building blocks of any Excel spreadsheet. The formulas you use will ultimately determine how flexible your spreadsheet is. The effective use of Excel formulas allows advanced users to summarise large amounts of data, analyse the outputs and make recommendations based on the results. These are the must have formulas:
INDEX AND MATCH
Editing formulas / Error checking
Sound use of formulas and knowing which one to use in any given situation is the foundation block of Advanced Excel knowledge.
Advanced Charting Techniques
Being able to use the right chart in the right situation is key. When you are comparing datasets there are certain charts that work well. Once you understand what message you're trying to tell with the data you have, it's time to select the best method for displaying that information on a page. Different chart types cater best to different methods. To create a comparison chart, use these types of graphs:
Circular Area Chart
Scatter Plot Chart
All of these suggestions will get you started, but there are no hard and fast rules for how data should be presented, it should be clear, communicative, and need no further explanation. If you find that you're restrained by common chart types, then by all means branch out to more experimental techniques. There's no reason not to let your inner designer sit down with your inner statistician—together the two of you can come up with some intelligent and informative methods to present information, and you won't have to fall back on pie charts and bar graphs to do it.
Conditional formatting is an inbuilt Excel feature when used effectively can be visually and operationally extremely powerful. Conditional formatting highlights either areas that exceed, meet or fall short of expectations most notably with a traffic light effect or the use of colour. There are inbuild icons which make conditional formatting fun and attractive on the page. They are remarkably simple to apply and adjust to add your own criteria. Used intelligently conditional formatting can be a great way to flag issues in business or processes which can prompt a decisive action.
Tables & Formatting
An Excel table is a way of structuring data in a dynamic way inside of an Excel spreadsheet. Advanced Excel users know how to create and use Excel tables to manipulate and manage their data. In a table the headings will hold the reference to its own column so it is important as per normal to name the column wisely. Tables are an genuinely powerful resource which allows you to generate more data dynamically. The list can grow and any formulas that are associated with a table will grow as well. The formula length will adjust as the table either increases or decreases in size. Tables are a wonderful addition to Excel and well worth adding to your arsenal.
Pivot Tables and Power PivotPivot Tables
A pivot table an inbuilt Excel program that allows you to seamlessly summarise selected datasets to obtain a neat report in a matter of moments. A pivot table looks at the dataset you desire and summarises information based on the column heading. Advanced pivot table functions include one of my favourite things in Excel – the use of slicers to summarise data, other key features include grouping, using different features such as summing, counting, average, min, max.
Power Pivot is the big brother of the pivot table. With Power Pivot you can store an enormous amount of data from multiple sources (not just a spreadsheet) into Excel back end and summarise multiple tables into a single pivot table. It is this advanced functionally which gives power users a genuine advantage.
Short Cut Keys
I can not recommend highly enough learning Excel’s shortcuts. As an Excel user you can increase your productivity and achieve much more by using the keyboard as opposed to the mouse. If I were to select say 20,000 rows by 20 columns the task would take 10-15 seconds with the mouse – if however I selected Control + Shift + 8 (top keyboard 8 not number pad) then it takes the time to press those 3 keys – a fraction of a second. The data needs to be structured in a tabular format but that is a given for advanced Excel users.
Goal Seek & SolverGoal Seek
In Excel goal seeking is the ability to calculate a problem by iteratively testing solutions until the correct answer is reached. This is often referred to as what-if analysis or back-solving a problem. The beauty of mastering the technique is you can get optimal solutions to difficult problems in moments without having to run the trial and error yourself. It is worth pointing out that Goal seek works wonderfully well with VBA as usually more than one cell needs a goal and VBA will do many cells in no time at all.
Excel has a really cool tool called solver that uses trial and error to find optimal solutions for a range of operational problems. It is a sort of WHAT IF analysis and is genuinely useful when determining the optimal outcome, given a set assumptions.
Solver is an Excel Addin which will need to be incorporated into Excel.
Visual Basic for Applications (VBA)
The language that sits behind the Excel spreadsheet is known as Visual Basic for Applications. With knowledge of VBA and advanced user can make Excel do some amazing things. Tasks can be automated, new formulas can be created and time consuming reporting processes can be done by the computer not the individual. Once the knowledge is acquired the time savings can be enormous.
Finally I have completed a broad range of new Excel Dashboards. It has taken me most of the month to create and distribute my Excel Dashboard works. Firstly I put my ideas on paper by drawing up a sketch of what I a want to achieve, usually with pictures either from my library of material or a concept I want to make with a chart. Then I go about putting the raw data together building the calculation tables and specifically designing the spreadsheet so that whatever I want the dashboard to ‘spin’ on I include this metric through ALL of my formulation. It can be a long and drawn out task but when I get to the end and I have stunningly beautiful models like the following I am usually well pleased and ready to tap my creative skills to make bigger and better Excel dashboards in the future.
The following are a selection of my premium Excel dashboards which are available on my Eloquens channel. These are the most advanced Excel Dashboards I have created to date. Enjoy!!!
The poster at first tried to choose the Specialcells method (a snipers rifle if you will) but could not get it working so checked all the cells in a selected range (a shot gun by comparison). The reason why the first method would be better is Excel will surgically concentrate on only the cells that have formula in and ignore the rest of the cells. The latter method will check all of the cells in the selected range.
The following are the values that need to be entered into the input box.
xlAbsolute 1 Convert to absolute row and column style.
xlAbsRowRelColumn 2 Convert to absolute row and relative column style.
xlRelRowAbsColumn 3 Convert to relative row and absolute column style.
xlRelative 4 Convert to relative row and column style (no references)
The following is the code I wrote to solve the problem. It works nicely.
Dim rng As Range
Dim i As Integer
i = InputBox("Add a number between 1 & 4", "Goski")
For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
rng.Formula = Application.ConvertFormula(rng.Formula, 1, 1, i)
Place the VBA code in a regular module and select the range you wish to affect.
So I have a very important Excel workbook and I need the original formula protected at all costs. So I want a process that runs through all of the worksheets in the Excel workbook and converts everything from formulas to values. This way none of the mechanics can be messed with in any way. This is a bit like sending out a read only version of your work. Or converting the whole thing to PDF. There are many ways to skin the preverbal cat as it were.
Dim sh As Worksheet
For Each sh In Sheets
Application.CutCopyMode = 0
The For Each loop covers off all the worksheets in the workbook and the used range covers off only the used data in the workbook. After this the paste special xl values takes over to finish the job.
The process runs reasonably quickly. It will clear the file. You want to make sure that you save the file as a different version and please have a back up just in case you accidently save the file.