October 2016 Webinar: Dashboards And Dynamic Charts In Excel

This October's monthly webinar will be for Wednesday, 26 October 2016. I will be walking us through the concepts of Dashboard and building dynamic charts in Excel.

Dashboards are clear visuals of the insights in your reports in a consolidated and easy to understand way. Just think about your car's dashboard. In one glance you get to see the state of your car. And that is the way your visuals should be like if you are making reports on a system as complex as a car, having many intricate moving parts and lots of KPIs to track.

Maybe you are a sales analyst and you track over a dozen brands/products across dozens of regions/areas. Your final report is a labyrinth, even you sometimes get lost in it.

Maybe you make a fairly simple report but the lines of records are many and regular charts look so crowded/clumsy that you have given up including charts in the report.

Or maybe you want to make more engaging visuals, put in some element of interactivity. Make the data come alive. 

Then you shouldn't miss this webinar.
Date: Wednesday 26 October 2016
Time: 4:00pm UTC+1 
Venue: https://www.youtube.com/watch?v=MfnSWzwU5JY (YouTube live)

We run webinars monthly on how to improve your business data analysis skills. To be among the first informed monthly about the webinars and get timely reminders, please sign up here: Webinar Directory.

The Errors You Encounter In Excel Are Telling You Something Important.

If you've used Excel consistently at work for a few months, you will be familiar with some errors Excel displays when it can't get you the answer you want. Oftentimes, we don't give some thoughts to these errors but the truth is they are not random or meaningless, they are trying to tell you something important.

Today, I will be sharing with you how to interpret those errors and uncover the gem in them. How to read the message they are trying to pass across to you.

There are eight error types in Excel:

1. #VALUE!
2. #DIV/0!
3. #N/A
4. ########
5. #NAME?
6. #REF!
7. #NUM!
8. #NULL!

#VALUE! Error

You get #VALUE! error when you do a calculation in Excel that doesn't make sense. Like Michael + 2. What is Michael + 2? Is Michael a number? What kind of answer are you expecting? Can you try it on your CASIO calculator? 

To be blunt, #VALUE! is Excel's way of saying someone is stupid. Someone has typed in a calculation that makes absolutely no sense. 

#DIV/0! Error

As the name implies, division by zero, #DIV/0! is the error you get when you do any formula that divides a number by zero.

Now, I have a trick question for you: what error do you think Excel will give you when you divide Michael by zero? #VALUE! or #DIV/0!?

#N/A Error

This is the error you get when you do a lookup function (VLOOKUP, LOOKUP, MATCH etc.) and Excel can't find what you are looking for.

####### Error

You get this error for two reasons. The first and more common one is when there is not enough space in your Excel cell to display a numeric (number) value. Excel doesn't want you to take 1,000,000 as 1,000 because space was only enough to show 1,000. So it puts in ####### in the entire cell and force you to expand/widen the cell to see the entire content.

The second and less common one is when you do a calculation on date and the resulting date answer is too large or too small a value to be shown as a valid date in Excel. Here's what I mean. In Excel you can do 26-Aug-16 + 1 and you will get 27-Aug-16 (the next date).

But when you try 26-Aug-16 + 999999999 you get ######## and no amount of expanding the cell will make the error go away. You are trying to calculate a post-apocalyptic date.

#NAME? Error

This is the error you get when you type a formula name that doesn't exist in Excel. Maybe you wanted to type SUM but mistakenly typed SUN. Well, Excel doesn't know about the solar system so t will tell you that it doesn't recognize that formula name.

#REF! Error

This is a very popular and troublesome error. It happens when you have a formula that picks value from a different sheet or different file and somehow the file or sheet becomes inaccessible (or deleted). When Excel tries to recalculate the formula, it gives you that #REF! error because it can't access one of the reference file/sheet.

Another funny case that causes it is when you drag a formula that depends on a cell above it too way up that it messes with Excel's cell reference system.

#NUM! Error

This is the error you get when you do a calculation that is too large or too small for Excel to handle. An example is 999 raised to the power of 99999

#NULL! Error

This is a very uncommon error. We are all familiar with the multiplication, addition, subtraction, division etc. operations. There is one not very commonly known -- the intersect operator. And it is simply a space. It gets you the intersect value of two different ranges.

So what happens when you select ranges that do not intersect? You get #NULL! error.

And those are the errors in Excel and their interpretation. Now you know what they are telling you anytime you come across them.

August Webinar: Demystifying Data Analysis, Business Analysis, Business Intelligence and Big Data. No more confusion.

image: kdnuggets.com

There is too much confusion as to who exactly data analysts vs business analysts vs BI analysts vs big data analysts are.

In this month's webinar, I will be helping you avoid those confusion and hopefully help you find which you really want to develop skills in.

I will be sharing my practical knowledge interacting across the boundaries of them all and the tools used by each.

The date is Thursday 11 August 2016 and time is 4:00pm to 5:00pm.

It will be a hangout webinar like the July one and the hangout webinar link is: https://plus.google.com/events/c9jkmfp6j2oa1vr5bspnrlgpk8c

Just make sure you click on it that day (Thursday at 4:00pm) to be part of the webinar. I will also be answering all your questions. So create it as an event in your phone or gmail or outlook calendar to get timely reminder for the event. 

You shouldn't miss it!

To always be in the loop of future webinar announcements, you can register at www.urbizedge.com (see screenshot below for where to register).

Once you register, you will always be first to be informed about our monthly webinars and sent the recording of previous webinars.

New Chart Types In Excel -- Treemap, Sunburst, Histogram, Box & Whisker, Waterfall and Funnel

If you've felt limited by the charts in Excel or would like to do some of those amazing charts you see online, then you've got to try out the new charts in Excel 2016. And if you are in finance and always had to use a complicated way to create waterfall charts, well, those hard days are now over. Same also for our statistically inclined folks -- you can now create your histogram and Box & Whisker charts easily in Excel.

Today, I will be showing you what those charts look like in Excel. And if you are impressed enough to want try them, all you need is Excel 2016. Better if you have the O365 subscription pack one, I think those ones get pushed new updates first.

1. Treemap.

This lets you use rectangular bars of varying height and width to visualize performance or values of different entities.

2. Sunburst

It's a lot like Pie chart and Doughnut chart, main difference is that it automatically sorts the data to be visually represented in descending order clockwise.

3. Histogram

It is for showing frequency distribution. Those of us with some statistical background will be at home with it.

4. Box & Whisker

This is also a statistician's tool. It is for showing the spread of values for the different quartiles in the data.

5. Waterfall.

This is an accountant's staple. It shows incremental effect of connected data points.

6. Funnel 

If you are into sales you will be familiar with funnels. How many leads you need to get a sale? Conversion rates? With a funnel chart you can visually depict each level of your sales activities and their conversion rates.

And those are the new chart types in Excel.

You can access them natively from the chart menu.

A Practical Introduction To Excel Macros

If you have got a recurrent report you make in Excel that follows the same steps, uses the same input type and the final report has a non-changing standard structure, then Excel Macros should be your friend. With Macros you can automate the report making steps so that every time you need to make the report, you simple provide the new input data, run the Macro and you'll have in seconds the output that probably used to take you hours to make.

The best part is that Excel Macros aren't that hard to make. Just like Excel, every level of user can make some productive use of it -- be you a beginner, intermediate or advanced Excel user. Also, it is with use of it that you become more proficient and maybe become like me, making money from building Excel Macros for companies.

Today I will be taking you through a practical example that will start you on your journey to harnessing the power of Excel Macros.

You can watch the video for this practice here: https://www.youtube.com/watch?v=yHzAPGqT4f8 and the practice file is here: http://tinyurl.com/ExcelVideo23

To make the example sink well, let's assume you work for a car dealership and you are in charge of sales analysis. The company is obsessed about branding. They've got the office built to reflect the company brand, the security men even wear uniforms that are the company's corporate colour. The part that worries you though is that all reports are meant to have a particular company style format.

Font 12, Arial, Dark Blue and All Borders. That's the style format. 

There are many ways, in the practical world, to automatically apply those formats to your monthly sales report -- from creating custom styles to using copy paste formats. But today we will use Excel Macros. It is a perfect example to introduce Excel Macros without boring you will tedious irrelevant work.

So here is the table of the entire year's report. You will now use Excel Macro to store the formatting process and apply them whenever you want at the click of a button or keyboard shortcut.

First, you need to enable Developer menu.

Go to File, Options, Customize Ribbon and tick Developer in the middle right of the dialog box you get. Click on OK.

Now you have all the tools needed. You will be able to see a record macro button at the bottom left of your Excel and a Developer tab on your menu bar.

Next step is to have a Macro created for the steps we do to apply the company standard format.

Select the January's data, we will use Macro Recording to store our steps. Click on the record button at the bottom left.

Give the macro a name. Start with an alphabet and don't put space or any special symbol except underscore. Let's also tie it to keyboard shortcut CTRL + m (m for macro, but you can use any you prefer, just avoid using common ones used by Excel like CTRL + C or CTRL + V)

Now your steps are being recorded. Apply the company format style of Font size 12, Font type Arial, Font color deep blue and all borders.

Click on the Stop Macro record button. same button you clicked on to start the macro recording.

Let's test for another month -- February.

Select February's table and do CTRL + m. If you followed my steps correctly you will see Excel automatically repeat all your steps and apply the company format to the February table.

Congrats! Now you've created a Macro. You now understand the process. Next is to use the same concept/steps to create one more relevant to you. Automate those repetitive tasks you do. If I had a more complex task, it would have recorded it too and repeated my steps whenever I called on it.

Finally, let's tie the Macro to a button.

Click on the Developer tab, Insert and choose button under the Form Control section.

Draw a rectangle, in the dialog box that will pop up, select the macro you created and attach it to the button by clicking on OK.

Give the Macro button a name. And now test it by selecting March table, then click on the Macro button to watch it apply the company format to March table.

Finally, save your Excel file as a Macro enabled workbook. 

Congrats on your new Macro!

How To Add Additional Charts (Custom Visuals) To Power BI

Yesterday was demo day for me. I did a demo of Power BI for a client for another client. It was the third time I am officially demo-ing Power BI to a large company, and increasingly companies are beginning to give Power BI serious considerations.

In today's post I will be focusing on custom visuals for Power BI -- new chart types you can add to Power BI.

Power BI comes with 27 visualizations (charts), you can easily increase them to over 70 by installing custom visuals, like I have done.

And they are very easy to install. Just head to https://app.powerbi.com/visuals/ and download the ones you like (I recommended at least the Word Count visual).

Next is to import to Power BI Desktop.

And that's it! Now you can add as many custom visuals as you want and create very beautiful dashboards to stay on top of your business data.