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.

Creating A Custom List In Excel; An Interesting Example.

If you are an average or above average Excel user, you will be very familiar with Excel autofill. A common example is you typing numbers 1 and 2, then dragging to have Excel do to 10 for you. Or having January and February, then dragging to have Excel do the other months of the year for you.

What if I tell you that you can create your own list. A list of states in Nigeria which you can autofill whenever you need to recreate them. Or a list of your products; you simply type the first two and drag to create the rest (autofill). It even helps you with sorting in the order you've created the list. This can be super useful for companies that use codes for aspects of their operations -- base station address code, branch code, product code and so on. Caution: The custom list has a quite limiting size limit. 

So here is the example of how I created a custom list of states in Nigeria, arranged alphabetical order.

First, I have the states typed out in Excel, in the order I want it (alphabetical order).

And we are half-way done to having it permanently in Excel as an autofill-able list.

Go to File, Options, Advanced, and Edit Custom Lists.

In the Custom List Dialog box that comes up, import the list of states you already have typed into Excel.

And you are done.

Now in a new Excel file or Sheet, type in the first two states, select them and drag down. Excel will start autofilling the other states.

Congrats! Now you've created a custom list. No more typing out states in Nigeria, just type the first (or first two) and have Excel autofill the rest for you.

Time Saving Use Of Excel's Advanced Filters In Working On Your Business Data

Every time you apply filter to a dataset in Excel and then copy out the filtered data, you could have saved yourself time and some mouse-strokes by using advanced filter. And despite the name, advanced filter, it is surprisingly easy to use.

As an example, I have the fictitious sales record for Dhormino's Pizza for June 5, 2016. They sell 16 Pizza types: Meatzaa, Extravaganza , BBQ Chicken, Hot Veggie, BBQ Philly Steak, Chicken Feast, Chicken Suya, Chicken Legend, Beef Suya, Margarita, Italiano, Pepperoni Suya, Veggie Supreme, Hot Pepperoni Feast, Chicken Bali and Pepperoni Feast.

They got a new country manager this March, and one of his strategies is to have four brand managers owning 4 different baskets of the pizza types, like the FMCG companies do. Two of those managers are Michael and Mary. They are to ensure that the brands (pizza types) they own are performing well. They are given the latitude and resources to promote them using whatever promotional mix they want. 

As the company's main business data analyst, you pull out daily sales records and extract the ones for each brand manager. With advanced filter, it will be a piece of cake. You might even hook it to a recorded macro and at a click of button all is done.

Continuing with the example, let's see how to extract for Michael and Mary.

Step 1 is to write out the pizza types Michael and Mary manage, separately. And to help advanced filter know where to look for those pizza names, give them a header that matches that in the sales table. See the screenshot above for what I mean.

Step 2 is to go to Michael's sheet (another Excel sheet where you want to put Michael's). Launch advanced filter from there. It is under Data menu, about the middle of the menu tools.

Set it to "Copy to another location"

Provide the sales data range as the "list range", and Michael's pizza types with the header as the "Criteria range".

Finally, for the "Copy to", go to Michael's sheet and select where the result should be displayed. In this example, I select cell A1.

And voila! We get the results.

Very easy and cool.

And there is more to advanced filter. How about if we have a more detailed sales transaction data that captures sales value for each sales transaction, and we would like to extract sales transactions that generated more that 15,000 naira. That too is very easy with advanced filter.

Just follow me. Below is the snapshot of the detailed sales transaction data.

We have specified that we want greater than 15,000 naira transactions only.

The steps are just like previous ones. Launch the advanced filter and provide both the "List range" and "Criteria range"

Specify where the results should be displayed.

And voila, again! It's done!

Now you should be an expert, like me, at using advanced filter.