A lot of people feel making macros in Excel is extremely hard and should be left only to people who make a living from doing it full-time. If you are one of such people, I have a pleasant surprise for you. Macros in Excel is very easy and in the next five minutes I will guide you through making one.

 So just before we start, let me do a brief explanation of what a macro is, why you might need to make one and the benefits of being able to make one.

 Macros are simply a means of automating tasks in Excel. It’s no more than that. You might need to do it when you have a daily or weekly report you make that is of an unvarying standard format, input and output-wise. Having a macro can cut your analysis time from hours to 15 seconds. It’s like magic and everyone in your office will see you as a special being.

 To be able to make macros, you need to make a small settings change in your Microsoft Excel.

 Go to Files, Options and Customize Ribbon. Check the box beside Developer.

 
Now you will be able to access the Developer menu.

 

 And also the Macro record button, which we will use in this introduction to Excel VBA.

Next, I will show you how to create a macro by clicking the right button twice — the macro record button.

 I have prepared a sample illustration data.


 It is a fictitious table of Sales at an Autodealership by the different salesmen and the car make.

 So the task I will use a macro to automate is a series of formatting steps. 

 Note: For the gurus, it would be obvious that copy pasting format would have done the same thing our macro will do. Yes. But we have to do the illustration with something not too complex to confuse anyone. The good thing is that you will learn all the steps required to make any complex recorded macro you desire.

 So here are the easy steps to creating a macro.

 First, I select the month I want to manually do the formatting for and have the macro recorder save my steps.


 Click on the macro record button.

 

 Give the macro a name, a keyboard shortcut and a description.



 
Click on OK.

 Then begin doing the formatting steps. I change the font type, font color and add border, making it have our corporate color feel. Once I am done, I click on the stop recording button.


 And that’s all. We have created a macro. Next is to try it out and see it work.

 Select another month’s record and press CTRL + k (the keyboard shortcut we used for the macro).

 

 
Voila! It works!

 So let’s insert a macro button. A button you will click to run the macro. I am sure you’ve seen one before. They are super easy to create.

 Go to the Developer menu, Insert and select Button under Form Controls.

 
Draw a rectangular button where you want the macro button to be. Immediately, Excel will ask you to select the macro to link it to. Select the macro we just created.


 Click on OK.

 Then edit the name of the rectangular button.

And that’s it! You’ve created a macro button.

 Now select another month’s data and click on the macro button to see it work the magic we configured it for.


 See the result!


 Amazing, isn’t it?

 I hope you are now convinced that creating a macro in Excel is very easy.

 It’s now time for you to think up other creative ways to use a recorded macro.

 Bonne chance!

I have noticed that the elderly folks who live a very active life and are comfortable with the world around them are those who are not so rigid in their thinking and ways. They are constantly learning. They are the 60 year old on Twitter, the 70 year old writing a book and blogging, the 80 year old who actively uses his iPad and the 85 year old who uses his smartphone as well as a 20 year old.

 You might say they don't exist. Well, I have come across many. Maybe they are not so many in Nigeria and that is why most of our elderly ones do more of complaining than creating. But there are still those who are in the category I described above. They are always adapting to the new world, learning new things and creating something.

 There are a work in progress. Even at age 80.


And so I admonish everyone of us who aren't yet 50 to emulate them, Be a work in progress. Keep learning. Especially in fields dominated by youths. There are 60 year old web programmers. They learned what most people regard as just for the unmarried youth. There are 80 year old active novelists. They write a new book as often as they can. 

 The moment you stop learning the new hippy things and you stop creating any currently relevant work you are molding yourself into an elder who will complain about every thing and be out of sync with the real world.

 Don't give way to rigidity in your thinking. Don't try to have an answer for everything. Have questions. Ask questions. Be curious. Have an open mind. Keep learning. Be unashamed to make mistakes at what your 12 year old niece is great at. Learn continuously. Get on the web, it is the portal to the new world. Read, watch educative videos and expand your mind.

 Let everyday equip you with more tools/skills. Learn daily. Seek out knowledge. Be a work in progress. Always.
It is unfortunate that most things are cheaper in the US and come with almost no risk of being fake; and, sometimes, still cheaper after you add shipping costs.


For some time now I have been increasingly buying things online -- from Amazon and other US sites. The problem I initially faced was that they would not ship to Nigeria (for most of their non-book items). So I signed up to MyUS excellent shipping service for just $10 and have been using them to get my packages in Nigeria.

 They gave me a US address and contact phone number I can use when shopping on US sites. They then receive the package on my behalf and pick phone calls on my behalf. I get a notification when my package arrives their facility and I then choose the least expensive shipping carrier to Lagos (usually DHL and my package arrives in 3 working days).

 It was how I got my most satisfying online purchase till date: my iPhone 5. I first tried getting it on eBay, then eBay banned me for no reason. So I got on Amazon and got it for $409.95 when it was selling for over N140,000 here in Nigeria for the 64GB version I bought. And I got a US quality version not the Asia quality version. The glass on my iPhone's screen is thicker than that of all the other ones I have seen with friends and my iPhone feels sturdier.

 I also got my Microsoft Surface RT tablet's keyboard for $58.69 on Amazon. But it was not a very pleasant experience. Because of the size and stuffing, I paid more to ship it than I paid to ship the iPhone. However, it has been more reliable than the original one that came with the tablet.

 Currently, I have two great looking Timex watches on their way to Lagos for me. And guess where I placed the order? Jumia, Konga or Kaymu? Wrong! On Amazon. I would rather have the peace of mind of knowing I am getting the highest quality version of a product than risk getting the China quality version of the product, even if shipping makes it slightly more expensive.





There are many shipping services that provide you US address and package forwarding. It wasn't easy for me to settle for one. Some don't even require you to pay a $10 registration fee. But why I picked MyUS shipping service is that they have been around much longer than most and they are also arguably the biggest which translates to cheaper shipping rate as they are able to negotiate a cheaper rate with DHL, FedEx and other couriers. 

 It's better to pay $10 upfront and be guaranteed of the best shipping rate than to sign up for free on the others and get milked via higher shipping rates. 

 And that is how I order and ship things from USA.
Scenario Manager is one of Excel’s decision analysis tool. It allows you compare outcome for different business scenarios.

 Below is a practical business use case of the scenario manager. It is taken from our business circumstance and you’ll find it very interesting.

We run a Microsoft Excel and Business Data Analysis business. Our major income streams are consulting for companies on data analysis and business process automations, and Microsoft Excel training. So let’s say we decide to run a special one day Microsoft Excel training. It was specifically my idea. I had stumbled on a training advert on Punch newspaper. A one day training at VCP Hotel and costing N80,000. So I felt we should try it too. But I needed to build a convincing business case for the idea. And in doing this I used scenario manager.

 I called up the hotel to get the details of the cost of hosting a full day training in their conference hall. I then went to work on the other costs that would be incurred in putting together the training. And below is a the sheet of the cost details.


And the underlying formulas are:


 As you can see, I have gotten every cost item listed; the estimated number of participants and the course fee too. But to build a convincing business case I need to create different scenarios. Maybe three scenarios.

  • Scenario 1: The worst that could happen if don’t market the training well and put the course fee enticingly low.
  • Scenario 2: The most likely thing to happen if we do our regular marketing and put up a fair course fee.
  • Scenario 3: What would happen if everything goes extremely well. Which will be our marketing aim.


 So how do you set up these scenarios in Excel? You use Scenario Manager.

 But first we need to use Named Range for the most important cells in our scenario. They are the Gross Profit cell, the Number of Participants cell and the Course Fee cell. In our scenarios we want to monitor what the Gross Profit will be for different combinations of Number of Participants and Course Fee.

 I hope you remember how to do Named Range. You simply select the cell or range, go to the name box and type in the name you want to name the selection as.



 We do same for Course Fee.

 
And for Gross Profit.



 Now, we launch the Scenario Manager.

 It is under Data Menu, What-If-Analysis. 

 



So let’s add the three different scenarios.

 I’ll start with the worst. Click on Add and give the Scenario name as Worst. The cells we will vary are the Number of Participants and Course Fee cells.

 


 Click on OK. 
It will ask you to set the number of participants and course fee. So based on experience, I know that if we do no serious marketing and set the price to N45,000 we can get 20 people. And that is the worst that can happen.

 


 Click on OK.

 Create a second scenario. Name it “Probable”. It will be what we will most likely achieve. Give the number of participants as 30 and the cost as N70,000. 

 Finally, do the last scenario. Name it “Ideal”. It will be our marketing aim if we decide to go ahead with the training idea. Give the number of participants as 40 and the cost as N100,000

 Once you are done your Scenario Manager dialog box would look like the one below.

 
Click on Summary. It will ask you for the Result cell to monitor. That is the Gross Profit cell.



 Click on OK.

 You will be taken to a new sheet showing the comparison of the different scenarios.

 

 And as you can see, I now have a convincing case to show my partners and make them agree to us organizing the one day training.

 That’s how easy and powerful the Scenario Manager is.
A lot of you couldn't join in for the webinar I did on Sunday. I guess it wasn't a very convenient day for most people and some didn't have enough data on their phones to connect live.

 Well, today I am sharing with you all the webinar materials: the PowerPoint presentation slides (download here) and the live recording of the entire webinar (watch here).

 I started with the theory of digital marketing, how it is different from traditional marketing. Then I talked about the required components to an effective digital marketing and personal branding. After that I proceeded to giving us a live demo.

 I started the live demo with LinkedIn. I explained using my profile page how you should optimize your LinkedIn profile to attract eyeballs and especially that of recruiters and business clients. I gave testimonies of how LinkedIn got me my second job that launched my Excel consulting career and how it is constantly getting me a stream of clients (now that I run my own business).

 I then talked on how to get a one month free LinkedIn premium account and a hack to get the most value out of it. I went on to explain how to set up an advert on LinkedIn. How to target it to people in a particular location, industry, age bracket, job function and company size. I did a practical demonstration of this.

 We moved on to Facebook. I explained how to set up Facebook adverts for dirt cheap. And also target it to as narrow or broad an audience you want. I gave practical comparisons of the conversion rates I get from LinkedIn vs Facebook ads.

 I showed the Twitter account I have that has over 31,500 followers. I explained how it came to be and gave some very valuable practical tips you shouldn't ignore. 

 I also answered questions you will benefit from. In all, it was a very educating session and you now have the opportunity to access everything: the presentation slides and the webinar video recording. 

 Don't miss out of our next webinar! Register to be notified always: Webinar Directory Sign Up.

Without intending to I have joined the hackintosh community, a community of people who install Apple's Mac OS on their non-Apple PC. The image above is a screenshot of my Samsung laptop and as you can see, I have Apple's OS X Mavericks running on a VMWare and already upgrading it to OS X Yosemite.

 So how did I get into the community?

 It all started when I registered for an online course on building iOS apps. As you know, my strategic aim is to move into the enterprise business apps space and replicate the solutions I build for clients in form of an app they can use on their smartphones and tablets.

 I originally thought I would need to buy a Macbook as it's general knowledge that Apple won't let you deploy to its app store from a  Windows or Linux PC. Fortunately, as I progressed through the course I found out that I could have the Mac OS without buying a Macbook.


And that's how I joined the hackintosh community.

 But beyond learning to build iOS apps on the Mac OS, I would be trying out the newly released Microsoft Office 2016 for Mac. I would be building Excel programs that would be Mac compatible and also training people on using Office for Mac.

 Expect to see more Mac specific posts from me.
Making money online is very easy and straightforward only after you've done it. But before then it is very frustrating.
image: learntoearntuts.com

 Today, I'll be sharing with you ways I have used to make money online. I'm hoping you will find one or two ideas you can use in your own quest to make money online.

 Blogging
I have a blog and it makes me money. Last month it made me $106.89 from adverts. And it has gotten me several other business clients and opportunities that I can't easily fix an amount to.

 My advice is that go for passion over profit. Because of two main reasons: number 1, the profit never comes early enough; and number 2, only passion will keep you going happy and proud. You wouldn't want a blog that you're not proud of, one that is just there to make money and talk about things you have no interest in. That would be a self-inflicted punishment.

 When you blog, you open yourself up to opportunities. You connect with people without having to spend expensively. The people you connect with will remember you more than those you spend money to meet at a networking event. That in itself is a big value that has a $$ tag. And if you don't quit you will also make some money from adverts (google adsense is most recommended).

 Selling Things Online
I sell iTunes card and other gift cards online. Without any stress and in as little as 5 mins I can complete a full transaction and make a gain of a few dollars. 

 Then I sell our Excel training online. This has pulled in a lot of money for my business.

 Affiliate Marketing
I am a Konga Affiliate marketer. Here's my affiliate link and subaffiliate sign up link. I have 39 subaffiliates. I am yet to make more than the N500 sign-up bonus but I know that it's just a matter of time before some money begin trickling in. Also I was on the Jumia affiliate program, made some money but it was reversed so I pulled out.

 I am also an Amazon Affiliate marketer. Just that it didn't make me any money so I stopped my promotional activities.

 Freelance 
I joined a couple of freelance sites: upwork.com, fiverr.com, freelancer.com and guru.com. I have made close to a $1000 from them despite not being so active. And it has gotten some offline contacts and business opportunities.

 And those are the ways I make money online. 

 And currently I am working on selling my books on Amazon and this will be another source of online income.

Today, this evening at 6:00pm, I will be showing you how to build your personal and corporate brand online. And also how to make the most of the digital marketing world.
image: pofitec.com

 I will show you how I grew a twitter following of over 31,000 followers on one of my twitter accounts, an account I wanted to shut down in 2012 because I was frustrated. 

 I will show you how I built a very impressive LinkedIn profile. How it got me the job that turned me into the Excel guru and consultant I now am. And how I have kept getting opportunities via LinkedIn. I will also show you how I place adverts on LinkedIn and set up my company page.

 I will show you how to run adverts on Facebook for dirt cheap. The strategies to help max out the ROI.

 I will show you how I have built my business marketing on digital marketing and have it work for me. I will show you the benefits of digital marketing over traditional marketing.

 Below are the first three slides in the PPT I will share with you after the webinar. So don't forget to sign up (by replying this post or registering here). The time is 6:00pm today and all you need to join is to go to www.join.me/urbizedge at exactly that time.





This month is a great one for Microsoft. They are launching several products this month.
image: ibtimes.co.uk

 July 9: Microsoft launched the Office 2016 for Mac. Now Mac users can enjoy the goodness they missed since the Office 2011 for Mac. If you are more interested in the details and how to get it, head here: Official Release of Office 2016 for Mac.


July 20: Visual Studio 2015 official release date. This will be of more relevance to programmers who use Visual Studio. Microsoft is making great strides and getting more innovative. You can expect a lot more than what was available in the Visual Studio 2013. Head over here for more on this: Visual Studio 2015 release date
image: jiffie.blogspot.com

 July 24: Power BI graduates from the preview version to a fully functional generally available version. On July 24 we will be able to download an updated version of the Power BI designer and access a lot more functionality. You can read more on it here: Power BI becomes generally available.
image: blogs.microsoft.com

 July 29: Windows 10 launch. The highly anticipated Windows 10 will become fully available on July 29. You can check out the amazing new features it has and even reserve a slot for a free upgrade: Windows 10 
image: microsoft.com

 And those are the big events Microsoft has lined up for this month.
There will be times you need to extract a portion of a cell’s entry. A practical case was a template I built for a telecoms company to determine the least cost partner to use for each international call destination. So I had to use a formula to pick out the country codes and check which provider is the cheapest to use to each destination. 

I have prepared a sample data for a simple illustration. It is the matriculation number of the university I attended. It is a clever combination of department name, year of admission and candidate number.



The first three characters are the department acronym. The two digits sandwiched between two forward slashes are the year of admission and the last four characters are the candidate number.

We are going to use LEFT to extract the department name, RIGHT to extract the candidate number and MID to extract the admission year.




It is a very easy to understand formula: =LEFT(A5,3). You simply specify the cell you want to extract from and specify the number of characters you want to extract starting from the leftmost character.
In this example, it’s three characters we want to extract starting from the left (beginning of the cell entry).

Now let’s proceed to extracting the candidate number. This time we want to extract starting from the right, four characters. So we will use RIGHT.



=RIGHT(A5,4)
Also very easy to understand.

Finally, let’s extract the admission year. It requires the MID formula. It’s a little not easy to grasp like the LEFT and RIGHT. It requires that you specify the starting point for the extraction. The concept is very easy to understand, the part that trips a lot of people up is how the starting point is determined. You have to count from the first character (from the left) to the first character you want to extract. 

In this example, we will count till the first character of the year. It is the character number 5. Then you’ll proceed to specify the number of characters you want to extract (2 in our case). 






=MID(A5,5,2)
A5 is the cell we are extracting from. 
5 is the starting point.
2 is the number of characters we want to extract.


Don't forget to register for our next special Excel Training here: In-depth Excel and Business Data Analysis Training