3-hour interactive workshops
Attend our new 3-hour interactive workshops each month:
- Learn to work with tools that are job specific
- Save costs
- Spend less time out of office in great learning environment
- Work with an Excel Master with real world experience
Do you think you have what it takes to be the Excel World Champ?
The very popular Excel competition is back. Last year the Microsoft sponsored competiton #ExcelChampSA was very successful with hundreds of applications received. The grand prize last year was a DELL Laptop, however this year, the stakes are much higher, as the top prize will be a trip to Seattle, USA!! Continue reading
VLOOKUP lets you search for specific information in a Worksheet. For example, if you have a list of Product Codes with Prices, you could search for the Price or actual Item for a specific Code. It also lets you search between different Worksheets.
Its soooo easy when you know how!
- Highlight the range you want to analyse (on the OLD sheet) and name the Range. You will then have a range with absolute cell addresses ($ sign) in the formula. Makes copying easy as anything.
- Count the columns in the highlighted range before you do the formula.
- Go to the NEW sheet, click where you want the answer to be. Insert the VLookup formula.
- Complete as follows:-
- Now copy the Formula down. And Hey Presto! You have added the data from the OLD sheet, without you having to do a Find, Copy & Paste – through thousands of lines!
(If you already knew how to do this, please enter the Excel Microsoft Champ of South Africa Competition at #ExcelchampSA)
Excel 2016 and Power BI are a portfolio of tools that is unique in the industry. Together, they provide a broad set of capabilities that allow business analysts to more easily gather, shape, analyze and visually explore their data.
Today we unbox Excel 2016’s new and improved business analytics features, so you can take full advantage of each capability as it applies to your unique business needs. Excel 2016 improvements have been designed with the analyst’s journey in mind, from gathering to consuming data:
Faster and easier ways to get data
Before analysis can begin, you must be able to bring in the data relevant to the business question you are trying to answer. Excel 2016 now comes with built-in functionality that brings ease and speed to getting and transforming your data—allowing you to find and bring all the data you need into one place. These new capabilities, previously only available as a separate add-in called Power Query, can be found natively within Excel 2016. Access them from the “Get & Transform” section on the Data tab.
Sparklines, Conditional Formatting, Slicers and a few simple but useful formulas you may not have noticed.
Sparklines, first introduced in Excel 2010, are charts that provide simple visualization representations of trends across a row of your data—in a single worksheet cell. Sparklines offer excellent real-estate savings on crowded dashboard worksheets and can be extremely insightful for the amount of space used. This feature is unbelievably cool and ridiculously simple to use. And yet, not many analysts capitalize on these powerful tiny charts.
To create your own Sparklines, select the data range and on the ribbon click Insert and then select the Sparklines type—Line, Column or Win/Loss. Next enter the target range where you want the Sparkline displayed. That’s all there is to it.
Method 1 – Exact()
There comes a time when you will want to compare the contents of one cell to the contents of another – regardless of where these cells are (same or different books).
If they are in two different workbooks, make sure that both books are open. Choose one of them to perform the comparison in.
Click on a cell that you can use for your comparison.
Use the function Exact(first cell, second cell)
That is all. If they are an exact match – you will see True in the cell. If they are not exact – you will see False.
Please keep a look out for the other ways of comparing data
We often get errors in our Excel spreadsheets – some intentional and others not. Once you have fixed all the unintentional errors, you may feel that you don’t want to display the valid ones.
There are two ways of hiding these errors – an easy way and the correct way 😀
The Correct Method
There is a formula called IFERROR(your formula or calculation, what you want the error code replaced with).
For example. You need to divide one value by another – but you do not have the 2nd value yet. You will receive a #Div/0 error when you enter the formula. If you put the formula in the IFERROR() function, you can decide what will replace that error. IFERROR(A2/B2,”-“) Now all your #Div/0 errors will show as dashes.
The Quick Method
In the newer versions of Excel, highlight the area that contains the errors.
- Go to the Home tab, Styles group and select Conditional Formatting.
- Select Manage Rules.
- Click New Rule.
- Select Format only Cells that contain….
- And from the dropdown select Errors.
- Click the Format button and make the font white.
- Click Ok
All errors will now be in white and will not be visible.
Which method should you use?
That all depends on what you need the spreadsheet for. Obviously the formula method is the best as it shows others how clever you are. The problem with the Conditional formatting method appears when you need to use the spreadsheet on a coloured background. The white writing will then be visible.
Now go and show the rest of your company how clever you really are.