Monday, August 21, 2017

Power BI #2: Getting To Know The Power BI Desktop

The Power BI Desktop is the main tool you would be using in creating Power BI reports. You can freely download it here from Microsoft




Once you are done installing it. You get a startup screen like the one below.


There are two major parts of Power BI Desktop you will need to get very familiar with:

1. The Designer part.



2. The Query Editor part.


Let's start first with the Designer part. It is the window you are presented with upon launching Power BI. It has four main sections.


  1. The menu section comprising File (for Open, Save, Options/Preference settings etc.), Home, View and Modelling.
  2. The Report, Data and Relationship section
  3. Page section (like Sheets in Excel), and
  4. The context based section that shows Fields and Visualization when you are in Reports, Fields only when you are in Data and nothing when you are in Relationship.
Now to the Query Editor. It is the exact equivalent of PowerQuery (now merged into Get & Transform Data in Excel 2016). Its main function is to help you wrangle data before they are fully loaded/downloaded into the Power BI. So instead of downloading a 16 GB database table and then specifying which fields/rows to keep and which to discard, you can do the specifying using just a preview of the data and only import just the very data you want/need. This is a life and time saver. And space/memory saver too. Then you can do some very interesting and complex stuff you can't do from the Designer part -- like merge or append data from different sources, unpivot and a few other things I find myself doing repeatedly on client/commercial projects.

You get to the Query Editor from the Home menu in the Designer part.


And it has four sections too.

  1. The menu section
  2. The Queries section
  3. The Data section, and
  4. The Query Settings section (which only shows up when you have/selected a Query)
And that is it for this second tutorial post in the new beginner to expert series I am doing on Power BI. Cheers!

Monday, August 14, 2017

Video Recording: July 2017 Webinar On Getting Comfortable With Microsoft Excel VBA

You can watch the video recording on YouTube at https://www.youtube.com/watch?v=pLOfPuwcUKI




For July webinar, we explored the powerful Microsoft Excel VBA. 

Too many people wrongly think that VBA is hard or for just for core programmers. 

I will be helping you  see the benefits of learning Excel VBA and then help you make your first steps into becoming proficient at Microsoft Excel VBA.

In the webinar, I showed:
1. How to record Excel VBA macros
2. How to edit recorded Excel VBA macros
3. How to carry out simple tasks with Excel VBA
4. How to work with Userforms
5. And introducing you to loops (If loop, For loop, For Each loop, Do loop, Do While loop and Do Until loop), they form the backbone of Excel VBA coding

Ultimately, I don't expect you to become proficient by just watching in this webinar. The goal is to get you started and comfortable with exploring Excel VBA. To take away any fear you have for it and replace those fears with a curious admiration.


If you have not, do join our webinar directory list to be kept aware of every future (monthly) webinars at http://eepurl.com/bKwoaT 

Cheers!

Monday, August 7, 2017

Lagos, Port Harcourt and Abuja: Executive Dashboards, Business Data Analysis and In-depth Excel Training



The business world is now data driven and every business professional must now be fluent in the language of data. William E. Deming had the most accurate way of portraying this new age: “In God we trust; all others must bring data.” Without data skills you will have a tough time influencing in the business world. You must learn to manipulate data, make compelling data stories and leverage data for insights.

UrBizEdge Limited, Nigeria’s leading business data analysis company is putting together this special training for proactive business professionals. This training is aimed at making you extremely good in Microsoft Excel, dashboard making, data presentation and business data analysis; teaching you with live business scenarios from our experience consulting for multinationals within and outside Nigeria. It's intended for Sales Managers, Financial Analysts, Business Analysts, Data Analysts, MIS Analysts, HR Executives and power Excel users.

You will get our high value materials, tea break + lunch, our branded DVD with over 20 training videos and practice files, training notepad with pen, a comprehensive training reference material, a training certificate from us (
UrBizEdge Limited, a registered Microsoft Partner), after training support and free refresher classes.

The training will be facilitated by a Microsoft recognized Excel Expert with Microsoft Office Specialist Excel 2013  certification and the only Microsoft Excel Most Valuable Professional (MVP) in Africa (there are just about 125 in the whole world and it is the highest level of recognition from Microsoft to an industry expert). We have had participants of our training from Citi Bank, Dalberg, SaveTheChildren, Mobil, Chevron, Vodacom, Nestle, Guinness Nigeria, Nigerian Breweries, Delta Afrik, LATC Marine, Broll, Habanera (JTI), SABMiller, IBM, Airtel, Diamond Bank, ECOWAS, Biofem Pharmaceuticals, Ministry of Finance, FMDQ, Schlumberger, Palladium Group, Nokia Siemens Networks and DDB.

To register reach Michael on 08089382423 and mike@urbizedge.com or Hannah on 08021180874 and hannah@urbizedge.com  or Opeyemi on 09020043560 and opeyemi@urbizedge.com to register. There is a class size limit.
Lagos Date: Friday  25th August 2017 to Saturday  26th August 2017
Lagos Venue: Kristina Jade Learning Center, 70b Olorunlogbon street, after Banex Hotel, Anthony Village, Lagos. 

Port Harcourt Date: Friday 22nd Sepetember 2017 to Saturday 23rd September 2017 for Port Harcourt.
Port Harcourt Venue: Aldgate Hotel, 20B King Perekule street, GRA Phase 2, Port Harcourt, Rivers state.

Abuja Date: Friday 27th October 2017 to Saturday 28th October 2017 for Abuja.
Abuja Venue: Hotel Rosebud, 33 Port Harcourt crescent, off Gimbiya street, Garki 11, Abuja.

The training outline is:

1) Data Manipulation in Excel 
We’ll show you, from a consultant expertise level, how to manipulate data in Excel. From data preparation/cleaning to data formatting the professional way. We’ll cover both the science and the art of data manipulation in Excel, and share very useful keyboard shortcuts and expert tricks that will speed up your productivity in Excel.

2) Data Visualization and Presentation in Excel
A picture is worth a thousand words. And in the business world, it is often the only way to not bore your audience and pass the valuable message you’ve uncovered in your data analysis. We will teach you the foundations of data visualization – from the different types of charts to when to use each of them. Then we will work through business samples to learn the art part of doing data visualization right. You will learn the rules of business data reporting via charts and gain from our industry wealth of consulting for businesses in this vital area.

3) Large Data Analysis: Pivot Table, Pivot Chart and PowerPivot
You should never say you know Excel if you don’t know how to use Pivot Table. It is that important. It is Excel’s premium tool for analysing large data – sales data, inventory data, HR data, transaction data and most business operations data. We are going to cover from the basics to the very advanced use of Pivot Tables. We will show you how to create dynamic reports with Pivot Table; how to overcome some of its layout issues; how to turn off the distracting controls in your final report; how to create calculated fields; how to create Pivot Charts; and the special tricks only a full-time Excel consultant can show you that will turbo-charge you Pivot Table skills. Then we’ll show you how we analyse data of up to (and even above) 30 million rows in Excel. Yes, in Excel. Heard of PowerPivot?

4) Business Data Analysis 
In this section, we teach you the secrets that separate the analysis experts from the people with head/academic knowledge Excel. It is one thing to know the different tools in Excel and it is another completely different thing to know how to expertly mix them together to creatively deliver value at high speed. As full-time Excel chef, we will show you the secret ingredients that make companies consult us even when they have Excel super users in their organization.

5) Executive Dashboards and Reporting
This is the level self-knowledge will not get you to. How do you create an uncrowded insightful visualization for a report with 1000s of rows? Then how about for your sales analysis report of many products and regions? How do you show the different interactions in your data? In short, how do you bring your data to life and take it from a boring confusing mass of text to an interactive exciting visualization? You have to come to get the answers.

6) Excel to PowerPoint
Management level reports are best presented in PowerPoint. When you’ve got a delicate story to tell, you have to guide your audience through a one idea/insight a slide PowerPoint. We won’t teach you how to design slides but we will teach you how to make your chart slides speak very loud. You will also learn the tricks of linking your PowerPoint charts to Excel. It will help cut down the hours you spend on weekly/monthly PowerPoint reports. We will also show you how to embed Excel files in your PowerPoint slide. No more sending separate Excel files when you can embed them right on the very slide you reference their data.

7) Excel VBA
Forget about all you’ve heard about Macros or VBA. Let’s show you how easy and exciting it is to break into the Excel VBA world.

Reach Michael on 08089382423 and mike@urbizedge.com or Hannah on 08021180874 and hannah@urbizedge.com or Opeyemi on 09020043560 and opeyemi@urbizedge.com to register. There is a class size limit.


For a taste of our high quality content, view our free tutorial videos at www.urbizedge.com/tutorials

What's New In Microsoft Office 365 Excel

Office 365 is updated monthly, so no more waiting three years for new significant features. Before now, you buy Office 2007 or Office 2010 or Office 2013 and then wait for the next major version for any significant feature change. You mostly get bug fixes and minor feature updates till that next major version that often takes three years. Now, with Office 365, you get a monthly stream of significant feature updates/additions.

And in this post I will be sharing with you some of the new features that have been added to Excel, in particular, this year. You can check out the exhaustive list of update details here: https://support.office.com/en-us/article/What-s-new-in-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426

1. Realtime Collaboration
In 2014, after I was taken for Nigeria Leadership Initiative Associate Fellowship and we were done with the two weeks seminar, we committed to doing a project and required some level of collaboration on planning the project phases and execution. We were all scattered across the world, many of us in Nigeria but the ones outside Nigeria seldom stay in one place/country. We used Skype and other tools that helped us work together in realtime. One of the tools was Google Docs. All of us could type into the same document at once and see what the other person did instantly.

Good news, you now have that in Excel. All you need do is save the file on OneDrive, share the file with your co-collaborators and right from Excel (not just the online Excel version), you can all work together on the file in realtime. There is an autosave that syncs yout work/changes instantly for others to see.



2. Easy access to Superscript and Subscript

For those in the technical or engineering line, no more long trips to putting a superscript and subscript in your Excel. Now you can just pin them to the Quick Access Toolbar and use in one click.





3. 3D Models

You can now work with 3D models in Excel. Really cool! Just visit the Insert menu.


4. Icons to make Infographics

Again, you'll find them under Insert menu. Now you can create your own infographics from scratch in Excel using representative vector images.




5. Customise the Default Settings of PivotTable

So you have a favourite way you like your PivotTable to look -- color theme, remove subtotal, tabular form instead of the terrible looking compact form etc. Now you can permanently set your PivotTable to look that way. Just follow my screenshot steps below -- File >> Options >> Data >> Edit Default Layout




Just set your own defaults.


Those are just ones I can make out time to share for now. I have seen lots of new stuffs in the Data menu, especially with the Get & Transform section of it. Same with the Insert menu. And there is a new standalone Add-ins menu.





Excel is becoming more exciting month-on-month!

Wednesday, July 12, 2017

How To Create A Dynamic List In Excel

Think about this: You have a list of products, the list grows with time, and you want your Excel chart of the products versus sales to automatically capture any additional products you add. Or maybe it is a daily sales report, and you want the chart to automatically expand to show the newly added dates.

Well, what you need to create is a dynamic list (proper term is range, but let's go one with the easy fathom "list"). 

It turns this


To this (without you re-making the chart or even touching the chart at all)


And can add life sweetening spice to your Data Validation lists.



Just think of all the magic that would do in some of your reports.

So how do you create these dynamic lists?

Easy. There are two popular ways to create dynamic lists in Excel. One is to use INDEX and COUNTA. The other is to use OFFSET and COUNTA.

Technically, you should choose the INDEX + COUNTA one over OFFSET + COUNTA one as it has better performance. Again, remember the keyword -- technically. Practically, the one you find easier to master is better.

INDEX + COUNTA

For the chart one, I used the INDEX + COUNTA one. 


For the list field I created a named range:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

At the core, it is
=A2:INDEX(A:A,COUNTA(A:A))

What the index part does is to return the cell reference of the last row cell in column A that is populated. 

INDEX(A:A represents a list/array of all the cells in column A while COUNTA(A:A) part instructs Excel to locate the cell in the row position equal to the count of all filled cells in column A.






Now you get how it works.


OFFSET + COUNTA

For the Data Validation List, I used OFFSET + COUNTA


The named range is
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Again, at its core the formula is
=OFFSET(A2,0,0,COUNTA(A:A)-1,1) 

Easy to understand.


We take A2 as the start or reference point, go zero rows down and zero columns right, then expand the rows (height) downwards by the count of non-empty rows in column A minus one to avoid counting cell A1 (which is used for the field header), then take one column wide (sticking to column A).

The result is



Finally

For the chart, I simply replaced the Legend Entries and Axis Label with the dynamic named range.






And that does the magic!
For the Data Validation List, I simply put =months as the source. (months is the named range I created with the OFFSET + COUNTA formula)



And that's all!

Now you should be a dynamic list guru :) 

Wednesday, July 5, 2017

Slides, File and Recording of Webinar On Predictive Analysis Using Microsoft Excel

You can view the YouTube recording of the webinar here. I talked extensively about the different major levels of data analysis -- Descriptive, Exploratory, Predictive and Prescriptive analysis. Then I focused on predictive analysis and the ones possible to carry out in Excel.

Some of you may already know that you can do moving average, simple exponential smoothing and regression in Excel. Well, in the webinar I showed that you can also use Excel for double exponential smoothing, triple exponential smoothing, sentiment analysis (using Azure ML add-in), decision tree (using an add-in) and other predictive analysis methods.


Here is the download link for the practice file and below are the PPT slides (in image format).







Wednesday, June 28, 2017

The Attributes You Need To Thrive In The Data Analysis World

image: laptimeclub.com
To successfully begin a data analysis career and thrive in it, you need the following attributes:

1. Lots of tolerance, especially when frustrated
The problem I see with many people who don't go beyond talking about becoming a data analyst is that they don't have much tolerance for slow progress. They want to learn something today and apply it tomorrow; and when they can see how to apply what they are learning, they give up.

When it comes to data analysis, especially if you are coming from a non-numerate field, you will have the initial problem of conceptualization. The big question of how do you know that this is what to do with the data? It is the first reality that hits you once you transition from doing guided practice to tackling a real-world problem.

Then the second problem you face is that the steps needed to battle each data analysis task are always different. So you can read many books, watch many training videos and do many guided practice, you will not escape being stuck on your first non-guided tasks because you will need to do something you've never done before. Rather than complain, just google the way out.

Also, you will have mood issues. There will be times you just won't be in the mood for any non-guided data analysis. The main reason why we consultants tend to know a lot more than in-company staff who might even have been in the field for many years before us, is that we don't do the same type of tasks day in day out. Almost every data analysis project I get requires deep fresh thinking and most clients are very scanty on details (some don't even know what they should want and entirely rely on you to think that out for them).

2. Love for the work
You will have to put in many hours of study, practice and thinking. If you don't love working with data, you will burn out quick. You must be okay with thinking about your data work at night and in your dreams.

3. Willing To Help Other People
Freely, at first. It is only by doing and in various circumstances do you get very good at data analysis. It is by helping others with their data analysis work that you get that valuable hands-on expertise.

4. Continuously Learning
There are no end to tools, algorithms and methods for carrying out data analysis. The person who stops learning new tools and methods will soon find himself no longer relevant. Even if you don't see any compelling reason to ditch your tool for the new ones, still learn them. You don't want to have too many knowledge gaps when that new tool suddenly becomes greatly improved and the new world standard.

And these are the attributes I think anyone wanting to thrive in the data analysis world should have.