The Seven New Charts Exclusive To Excel 2016

In August 2016 we wrote about the new chart types in Excel 2016. 

Since then, the list has grown slightly. Now we have seven new chart types in Excel 2016. 

The latest entry is Filled Maps. It allows you do a map of your location/state/branch/region/city/country data with a graduated fill to show the values (of any metric) across those locations.

The other ones are:
 Treemap

Sunburst

Histogram

Box and Whisker

Waterfall

Funnel

For extra commentary on the charts other than the filled map, you should read: http://blog.urbizedge.com/2016/08/new-chart-types-in-excel-treemap.html

The Recorded Version of Our December Webinar On PowerPivot

On Tuesday we did have the webinar on PowerPivot. For one hour I showed how to combine data from difference sources and easier transform them into ones you can use to create meaningful single page dashboards.

I covered using the Query Editor in Get & Transform panel in Excel. We pulled in over 15 report tables from Excel and one database table from SQL, and combined them to create a dashboard that ties everything neatly together.

You should watch the webinar here: Turbo Charging Your Reports with PowerPivot

For the demo, I worked on real data of the companies listed on the Nigerian Stock Exchange. And we created a dashboard that looked like the one below.

Don't forget to watch the webinar recording here: Turbo Charging Your Reports with PowerPivot

Interested In Learning The New Office.js Excel Add-in?

For some months now I have been digging deep into the exciting new world of Excel (Office.js based) add-in. What does that mean? In straightforward terms, it means the new powerful and exciting way of creating Excel add-ins.

However it has not been easy finding learning resources. In fact, most of the early push and learning I got where based on insider NDA content shared in the Microsoft MVP community. But not everyone will get that silver spoon feeding privilege.

So how would you, without direct content from Microsoft, access resources that will get you started on learning and building your own Excel web Add-ins?

That is the question I have set to provide answer to in today's post.

1. I will recommend you start with Michael Zlatkovsky's Building Office Add-ins using Office.js site

2. I will recommend you watch Michael Zlatkovsky's End-to-End Walkthrough of Excel JavaScript Add-in Development video on Microsoft's channel 9

Also make sure you download the solution file so you can practice along.

3. Get familiar with the code snippets for doing the common tasks you'll want to carry out at office-js-snippet-explorer/excel-snippets and Office 2016 JavaScript API Snippet Explorer 

4. Build some sample add-ins from scratch with guidance. Try out Build your first Excel add-in and samples with the codes from Office Dev Center.

5. Read more helpful documentation (Excel JavaScript API programming overview and Excel JavaScript API reference) and maybe buy the Building Office Add-ins using Office.js book.

So those are my goto resources. But ultimately, how far you go will depend on how much practice and non-guided add-ins you build.

Best of luck to both of us on this new exciting journey!

Data Cleaning Tools In Microsoft Excel

We held a webinar on Data Cleaning in Excel. Well, there was some technical glitches but I think we have managed to find out the root cause and way around the issues to prevent similar glitch in future ones.

You can watch the video of the webinar here, might want to fast forward the glitched part.

As a compensation, I will be taking you through the crux of what I shared in the webinar with plenty easy to follow screenshots.

1. Text to Column
Sometimes, you copy data from other applications into Excel and they don't get arranged the way you want. Maybe you copied customer data records with first name, last name and email and instead of Excel putting them in different columns/fields, you get them sandwiched into one field. How do you easily remedy that?

Well, I am happy to introduce you to Text to Column.

To fix this, go to Data Menu, Text to Column and in a matter of 3, 4 clicks you are done. Even for a hundred thousand records table.

2. TRIM
Got text data with the spacing irregularly done? No worries, meet TRIM.

It can turn records that look like this:

Into this pleasant looking one:

3. PROPER and UPPER
Got text or names written with all lower case or terrible mix of the cases? Time to fix them looking Proper or all in UPPER.

4. Go To Special, Blanks
There is something definitely special about Go To Special beyond its name.

Have you got records arranged disorderly and you want to align the arrangement. Take for example, the one in the screenshot below.

With Go To Special, select Blanks. Then right click on the highlighted Blanks and Delete, shifting cells to the left. And, voila! It is done!

5. Removing Duplicates
Say you have a table with duplicate records. How do you remove the duplicate entries, leaving one unique record?

You guessed right: by using remove duplicates.

Here is a sample problem to attack.

First, we'll make Excel show us the duplicate records using conditional formatting. It's not a requirement. Just doing it for fun or maybe you really want to see the duplicate records before deleting them. 

Now let's remove the duplicate values. Go to Data Menu, Remove Duplicates.

See result below. Sparkling clean. No more greasy duplicates.

6. Text 2 Number
Sometimes, you import or copy numeric data into Excel and they show up as Text. For you to run regular calculations, with peace of mind, on them you need to convert them back to numbers. 

If you are lucky, which is the usual case, Excel will show you a diamond tool tip to guide you in converting the Text numbers to Number numbers😀.

Say today you weren't that lucky. No diamond tool tip. How do you proceed, and considering there are hundreds or thousands of records to fix at once.

Well, use what I call Text 2 Number.

Just type 1 in any empty cell, copy it and paste special on the problematic entries but as Value and Multiply.

That fixes it in one step for you.

7. Go To Special, Errors
How do you select Error cells in your report and maybe replace them with something more meaningful or delete them all at once?

Again, meet Go To Special.

8. Find and Replace Formula Parts
Find and Replace can work also on formula components. I often use it in making giant templates or in cleaning up my formulas after considerable report structure change.

9. Spelling Check and Auto Correct

Surprised? Well, Excel does have spell check and auto correct.

And those are the Data Cleaning tips we treated yesterday during the webinar.

Don't forget to forward this to friends and family members who will find this useful and love you more for it. And you should subscribe to our webinar directory to be in the loop of all future webinar announcements.

Training: Data Analysis and Business Intelligence Using Power BI

Data is the new crude oil. And business success in the 21st century is heavily reliant on the ability to mine and use relevant data about consumers, internal operations, financial operation and industry trend to drive business decisions.

UrBizEdge Limited, Nigeria’s leading business data analysis company is putting together this special training for proactive business professionals who already have some experience with business reporting and quantitative data analysis.

We will be focusing on Business Intelligence and how to create BI reports that leverage participant’s current data analysis skills using Microsoft’s Power BI. Power BI enables organizations to have a wholesome understanding of what is happening at all operational levels of the company.

This training is coming up on Friday 2nd December 2016 to Saturday 3rd December 2016. It covers our industry recognized certificate, practice materials, required software, tea break + lunch and other training materials.

The training will be facilitated by an officially awarded Microsoft Most Valuable Professional (MVP). And we have had participants of our training from Promasidor, Citi Bank, Dalberg, SaveTheChildren, Mobil, Total, Vodacom, Nestle, Guinness Nigeria, Nigerian Breweries, Delta Afrik, LATC Marine, Broll, Habanera (JTI), SABMiller, IBM, Airtel, Diamond Bank, ECOWAS, Ministry of Finance, 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 to register. There is a class size limit.

Date: Friday 2nd December 2016 to Saturday 3rd December 2016

Time: 9:00am – 5:00pm each day

Venue: Kristina Jade Learning Center, 70b Olorunlogbon street, Off Alade Lawal street, Anthony Village, Lagos. 

Cost: N100,000/participant

The training will cover: 

1.       Power BI’s strength and weakness compared to the other popular BI tools

2.       Important concepts of Power BI

3.       Connecting to any type of data source (from structured to unstructured which will require some transforming)

4.       Getting data from existing services, organizational content pack, flat files and live databases

5.       Data Transformation (very broad and requires some knowledge of data analysis) and we will use DAX formulas too

6.       Creating relationships between the datasets and leveraging hierarchy (a.k.a. data modelling)

7.       Creating Reports

8.       Visualizations and the science behind choosing the right visuals

9.       Importing custom visuals (especially word cloud for sentiment analysis and other very useful non-native visuals)

10.   Creating dashboards

11.   Publishing Reports from the Power BI Desktop and pinning to dashboards

12.   Scheduling refresh

13.   Q & A Natural Language query

14.   Integrating with Cortana

15.   Live Dashboards

16.   Collaboration and sharing

17.   Printing the dashboard

18.   Analyzing the dashboard data (report) from Power BI service in Excel (new feature)

19.   Value Proposition to corporate customers

20.   Use case scenarios for entire company or business unit or departments

21.   Access from mobile app and setting data alerts (automated notifications when something of note happens)

22.   Lots of interaction (Q&A) and practice

 

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

You can also sign up for our highly educative tutorial newsletters at http://blog.urbizedge.com/ and read/download our Data Analysis Industry Report for 2016 for your company use.

Whitepaper: Data Analysis Industry Report 2016 - Nigeria

We are finally done with the whitepaper on the Data Analysis Industry in Nigeria for 2016.

You can download it here: Data Analysis Industry Report 2016 - Nigeria

Data Analysis Industry Report 2016 - Nigeria from Michael Olafusi

If you know someone who might benefit from the report, please do forward/share.

Also send me us your feedback or what you'll like covered in the 2017 edition.