Recording of June 2017 Webinar On Useful Add-ins You Should Be Aware Of In Excel

This is a webinar on the add-ins I use a lot.

I work primarily with Excel and do projects for different companies across different industries and different countries/continents. To make my work and life easy I often use some add-ins in Excel.

Some of those add-ins are enhancers, enabling me achieve faster what I could still have done without the add-ins. And some are indispensable, without them I couldn't have gotten the job done.

The add-ins are

1. Fuzzy Lookup Add-in
2. PowerPivot
3. Name Manager +
4. Solver
5. Data Analysis Toolpak
6. PowerQuery
7. Nigerian Market Data

You can watch the recording of the webinar at https://www.youtube.com/watch?v=vbKf76RqIp4

There were some issues at the beginning and somewhere in the middle, you might need to fast forward through those places till I am able to do an edit of the video (hopefully this night).

Enjoy!

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!

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

You can download the practice along file at https://drive.google.com/file/d/0B4XKk-Dstn-eYVF5QlRIT3N2cVE/view?usp=sharing

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!

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!