Microsoft has included some new formulas in Microsoft Excel that I call "magic formulas".

They are:

• UNIQUE
• FILTER
• RANDARRAY
• SEQUENCE
• SORT
• SORTBY

In today's post I am going to introduce you to SORT.

The name already says it all. And some people might wonder: why would anyone want to use a formula to sort?

Remember the first post in this series, where I talked about UNIQUE.

Wouldn't it be nice to have the output of the UNIQUE list of products sorted alphabetically? You can download the practice along file at https://urbizedge.blob.core.windows.net/urbizedge/SORT-%20new%20formula.xlsx

Meet SORT
Just wrapping SORT around the UNIQUE formula, you get a sorted unique list of products.

Cool, right?

The magic doesn't end there. SORT allows you to specify column to sort by, the sort direction and whether row-wise or column-wise.

Below I used SORT on the entire sales report table.

You can watch a short video demonstration of it: https://youtu.be/P8vJe9iW9VU

Microsoft has included some new formulas in Microsoft Excel that I call "magic formulas".

They are:

• UNIQUE
• FILTER
• RANDARRAY
• SEQUENCE
• SORT
• SORTBY

In today's post I am going to introduce you to UNIQUE. It can be the end of your troubles with PivotTable for use with growing/changing dataset. And it can be the butter to your SUMIFS, COUNTIFS and many other high-level report creation formulas.

Scenario

Imagine you need to create a live report of sales from raw data that keep getting updated (or is growing). Download the practice along file at https://urbizedge.blob.core.windows.net/urbizedge/UNIQUE%20-%20new%20formula.xlsx 

You could use PivotTable to create this:

The sad part is that whenever new data is added, you will have to manually include the new data range in the PivotTable  or do manual refresh if you had used "Format as Table" on your raw data before Pivot Table creation.

Alternatively, you could have used Remove Duplicates, SUMIF and COUNTIF. Again, upon new product addition, you'll have to redo the Remove Duplicates.

Meet UNIQUE

UNIQUE is the solution you want.

It does remove duplicate but in a dynamic way. If you don't want to select unused cells in order to capture new entries, then just apply "Format as Table" on your data. But for this demonstration, I am going to show without formatting as table while in the video I demonstrate both situations.

Simply typing UNIQUE(range_of_cells) creates the remove duplicate equivalent of the product field, giving me the unique list of products sold. 

Now I can merge it with other regular formulas like SUMIF and COUNTIF to get the sales per product and count of transactions. 

In the criteria, I select the entire output of the UNIQUE formula. This automatically changes it to first_cell_address_# (e.g. K2#). That # is a new feature to let you know that it's working on an array output.

I do likewise for SUMIF to get the sum of quantity and sales amount per product.

You can watch a short video demonstration of this: https://youtu.be/QLgwXVkALFg 

In April 2020 I will, alongside 13 other global experts, be presenting at the Olympia, London for the first Global Excel Summit.

I will be presenting on how to earn high consulting income in low income countries. An interesting topic. I figured since many people will be presenting on technical sides I should pick a topic I am uniquely experienced in.

And since the start of my Excel consulting journey in 2012, I have seen a lot and skillfully moved from getting peanuts for my expertise to charging as high as N357,000 for a 1h45mins talk. And close to a million Naira for a one day teaching job. Though now, there are lots of overhead, a few full-time staff and some expensive solutions we are developing that gulp all the income but without properly positioning myself to earn high consulting income, we would never have moved to having full-time staff nor developing mass market solutions.

Truthfully, it is not all my consulting engagements that pay well. The figures I quoted above are the outliers (rare exceptions). We still have some clients who do pay not much but we have figured out a way to get more than the industry average, what the typical consultant in our field in a country like Nigeria gets. We have figured out an excellent way to turn our consulting service to a high margin good volume product and attract the right type of clients in a way that we can get above market rates from them.

I would be sharing all those strategies during the presentation. I will also mention how I get, with no marketing work on my side, $55/hr at my free time gigs. 

To register, visit https://globalexcelsummit.com/ and you can use my discount coupon: Michael-10%

Ever thought of creating an interactive form in Microsoft Excel? With clickable radio buttons, dropdowns to select from a predefined set of options, an image file picker to attach/import a passport photo, a calendar that pops up to let the user pick a date and many other useful features. Today is your lucky day.

You can watch the video tutorial here: https://www.youtube.com/watch?v=SRUiFpbtBgQ

All these amazing tools are housed in the Developer menu. So the first task you have to do is enable/activate the Developer menu. Go to File, Options, Customize Ribbon and tick the checkbox beside Developer.

In the Developer menu, you will see the controls that make interactive forms possible -- from radio buttons to checkbox to calendar to picture frame and many more.

Watch the video to see me do a comprehensive live demo. And I included a bonus on how to make what the user type in one part of the document appear in other parts of the document. This is a neat trick for creating legal or contractual documents. You ask for the other party's name and that name appears in all the necessary parts of the documents. And once you change the name in the first part, it changes in all the other parts.

You can watch the video tutorial here: https://www.youtube.com/watch?v=SRUiFpbtBgQ

Originally, I shared this on a Telegram Finance and Investment group (which you can join via https://t.me/joinchat/Gfyf4VFBAOHYM3kdc98QHA 

I have been watching RealVision.com series on the global economy and gleaned the following insights:

The global economy is slowing down. All the numbers are indicating that Germany is nearing a recession, US manufacturing is down. Europe, generally, is having a very tough time due to many localised issues across the countries there and it is generally weighing down the European economy. Global banks are shrinking their activities to avoid 2007/2008 styled contagion of financial distress.

China is slowing down at a rate more than what the government officially publishes. For the last one year, the govt controlled financial sector has been pumping cheap loan out to create artificial economic wealth — keep comatose companies alive, keep companies expanding, fund large scale infrastructure and mute the effect of lower consumer activities.

US Dollar is considered as overpriced (too strong). The negative and zero bond yields in Japan and Europe has driven a lot of demand for US financial market assets. The emerging and frontier economies have felt it the most, seeing their currency depreciate significantly in the last 2-4 years. There is a lot of push to weaken the US dollar — from the White House as that will make their trade war with China (and other countries) more effective as per their desired economic effect, for the Fed as that could provide the next economic boost, and for investors as it means general asset price/value increase.

Gold is considered as being underpriced due to monetary policies that are aimed at reflating the financial markets whenever there's some downturn. Also, the strong US dollar is muting the price level.

Bitcoin is considered as very underpiced. Bitcoin has a market cap of $149B. If it were a private company listed on the stock exchange or a startup, it would have valuations in very many multiples of that. It's userbase is well beyond than of many companies with larger market cap, it's adoption rate is very high and it is powered by technology+infrastructure that runs into billions of USD. 

Another angle of view of some of the portfolio managers is that Bitcoin is not correlated with the broader investment market and provides a better alternative to holding physical gold as against gold futures that many still see as tied to the financial markets systemic risks. 

One important consensus is that Bitcoin is gaining more ground in the mainstream investment funds world. More institutional investors and family offices are holding positions in it. And this would only continue to accelerate.

There is a new global drive towards another version of QE that is built on Modern Monetary Theory (MMT). So the concept is to remove the classical limitation to printing more money and expansionary spending by government because of inflation worry. The theory states that inflation is not directly/naively linked to printing more money as usually said. And the last 2 decades is a solid proof. The theory says as long as it is not done past the level where the economy reaches full employment and GPD potential, the printed money would only unlock greater factor utilization and economic growth.

So now central banks will start printing money more than ever before. Christine Lagarde is regarded as a monetary policy evangelist, so ECB will do even more than would have under Draghi. Japan is tilting towards deflation and they are now going back into aggressive QE. China is also being propped with articifical money which they are now doing more of as they try to devalue the yuan to mute the effects of US tarrifs and weaker exports.

The worries are that there is no proven method to ensuring the money printed primarily impacts the real economy and not just inflate financial assets prices. One thing is sure though, there will be more financial asset price propping. Even if/when a recession hits globally, these artificial supply of money will not let prices be as depressed as they ordinarily would have been nor stay depressed for as long as they historically would have (pre- QE)


Practical takeaways I am considering are:

1. Do more Bitcoin holding. If US dollar goes up, my Naira value of Bitcoin goes up. And if US dollar is devalued, higher chance of seeing it reflect in my Bitcoin value than in holding plain US dollar.

2. Hold and add to my Gold positions. Strong fundamentals and narratives for good upside.

3. Stay clear of Oil. Weakening global economy (especially the manufacturing, logistics and fossil fuel based sectors) limit the upside to Oil. Even despite the Saudi Aramco facility bombing, Iran tanker hit and other issues that typically create upside speculation, Oil price has gone down to below the Saudi Aramco cut price surge even though there are narratives that there is more supply from reserves than is claimed by Saudi in supply/production recovery.

4. Stay out of Nigerian stocks until price/volume action indicates a strong price recovery. It is not just enough to spot high dividend yields, it's the future guidance (future dividend yield) that matters and when compared with capital loss vs capital preservation and sure Fixed Income yield vs unsure future Dividend Yield; I will want to be less optimistic about stocks till I see improved economic fundamentals/policies and return of investment capital to equities. 

5. Do more of money market. I don't see yields going down as we will have to struggle to keep inflation from not rising while trying to attract FPI's with good real rate of returns.

6. Keep my expenses as low as possible and in Naira. PPP vs nominal exchange rate favours spending in Naira. 

7. Derivatives on NSE. Watch out for the derivatives NSE is saying they are launching soon (they still said it during last month's conference type event).

8. Add to my US treasury bond holding. There is a general consensus that the developed world is heading to zero percent interest rate in the short to medium term. So far US is the only one not yet there. And as the rate differential is being arbitraged away, the price of its bonds will go up.

Last year's was amazing. And we are planning towards having a more amazing one this year.

You can register to attend via https://meetup.com/Nigerian-Excel-Users-Meetup/events/265613996/ 

If you are interested in giving a presentation, then kindly fill the presenter's form via https://forms.gle/g2A2faEfM5BA2k8N7 

The full details are (till we get the verified presentation topics and presenters):

Nigerian Excel Users 2019 Meetup

The Zone Tech Park, Plot 9 Gbagada Industrial Scheme, UPS b/stop, Gbagada Expressway, Lagos.
23rd November, 2019 (09:00am to 3:30pm)

Start Time: 09:00am

1) Introduction and networking (25 mins)

2) General Discussions pt 1(Your Excel Journey, career advantage, Excel pet peeves, ideas, the esoteric, 30 mins)

3) Presentation 1 (25 mins) + Q & A (10 mins)

4) Presentation 2 (25 mins) + Q & A (10 mins)

5) Presentation 3 (25 mins) + Q & A (10 mins)

6) Break (10 mins)

7) Presentation 4 (25 mins) + Q & A (10 mins)

8) Presentation 5 (25 mins) Q & A (10 mins)

9) Presentation 6 (25 mins) + Q & A (10 mins)

10) Break (10 mins)

11) Presentation 7 (25 mins) Q & A (10 mins)

12) Presentation 8 (25 mins) Q & A (10 mins)

13) General Discussions pt 2 (Your Excel Journey, career advantage, Excel pet peeves, ideas, the esoteric, 30 mins)

15) Close (5 mins)

End Time: 3:30pm (Networking continues)

There will be drinks and snacks.


Don't forget to register to attend via https://meetup.com/Nigerian-Excel-Users-Meetup/events/265613996/ 

A big thanks to all who were able to make the live broadcast. Especially, for bearing with the initial broadcast hiccups. You can watch the recorded session at: https://www.youtube.com/watch?v=CEAA7ar6xfM

Below are the slides and resources from the broadcast session. The next one would be on Equities Investing.

You can download the presentation slides at https://drive.google.com/file/d/14U6FNSPbqWGDZ5Lx11_ZKio_aUSwIqHX/view?usp=sharing

Here are the links to the tools mentioned:
Excel file for tracking expenses - https://drive.google.com/file/d/1413oVdQAtZQSQBgwaj_-rdN4nLUm8dBN/view

App for tracking expenses - http://wally.me/

Where to download real estate listing magazine - https://castles.com.ng/download-past-editions-of-castles-lifestyle/

Where search real estate price trends - https://www.nairaland.com/properties and (years past) https://www.nairaland.com/properties/1700 

Where to chek stocks and crypto price trends - https://www.investing.com/

Where to get stocks and macro-economic analysis - http://vip.urbizedge.com/economy

Join the large discussion community on Telegram via Telegram group at https://t.me/joinchat/Gfyf4VFBAOHYM3kdc98QHA and the meetup community at https://www.meetup.com/Financial-Modeling-Financial-Planning-Meetup-Group/


Enjoy!

I have tried DSTV, Showmax (https://showmax.com/), DSTV Now (https://now.dstv.com/), YouTube TV (https://tv.youtube.com/), Hulu (https://www.hulu.com/) and Netflix (https://www.netflix.com). It's only Netflix I renew every month.

In today's post I am going to share the exciting tips I have snagged over the years of using Netflix (even before it was publicly available to us in Nigeria).

1. VPN opens you up to amazing new contents.
If you have always logged into Netflix from Nigeria, you are missing out on a lot. There are many wonderful movies (especially new and hit movies) that are geo-locked. Just logging in to your Netflix account from USA or Canada or UK or France or Germany or Australia opens you up to new great movies.

So how do I achieve this without leaving Nigeria? VPN. Specifically, I use ExpressVPN (https://www.expressvpn.com). After trying many VPN providers, they are the only provider that consistently worked.

I usually set my location to USA or Canada, they have the largest collection of interesting movies and more recent hit movies.

2. Pick a genre
The new layout of Netflix tries to guess what you'll like to watch. The problem is that after a while you would have watched all the good guesses. Why not pick the genre you love and focus more on discovering the exciting movies in that genre that the homepage list omits.

3. History
Ever wanted to recommend a movie you recently watched but can't remember the title? It has happened to me many times. Now I simply go to my viewing history to get the name. It's under Account.

4. Search actor or actress' name
When I am getting frustrated with the movies I am being presented with, I just hit the search on a favorite actor/actress. Interestingly, I have done this on Jason Statham, Nicolas Cage, Tom Cruise, Denzel Washington, Johnny Depp, Pierce Brosnan, Mel Gibson, Ryan Gosling, Will Smith, Leonardo DiCaprio, Ryan Reynolds and Matt Dimon. I also click on their names in the movie details to pull up other movies they were in.

5. Avoid movies (not series) produced by Netflix
The movies produced by Netflix are mostly crap. I always feel like I wasted 2hrs whenever I watch them. So I just completely avoid them. However, their series are good. 

6. You can download to watch later on the PC and phone apps
If you have great internet access somewhere (I didn't say your office), you can download movies when there to watch at the airport lounge, at night at home and somewhere else you do not have cheap data. I have too much data and too little PC/phone space that I don't do this. Nevertheless, it is a tip worth sharing.

Cheers.

Occasionally, we are contracted to train on the general Microsoft Office. And I see people amazed by how much they don't know exist in Microsoft Word and how very exaggerated is their assessment of their competence in Microsoft Word.

Over the next couple of weeks, once a week, I will share with you some of the amazing things I have shown people in Microsoft Word.

For today, which marks the start of this series, I will be showing you a secret Microsoft Word formula that you can use to generate random texts in Microsoft Word. It can be very useful in creating templates and the skeleton of an important document.

You can watch my 2 minutes straight to the point video on it: https://www.youtube.com/watch?v=HP1V9VUDE9k 

The formulas are:
=Rand() 
This generates 5 paragraphs.

=lorem()
This generates 5 paragraphs too

And for both, you can specify the number of paragraphs and sentences in each. Just use the following format =rand(3,5) or =lorem(3,5)

The first number is to set the number of paragraphs and the second number is to specify the number of sentences in each paragraph.

I gave an extra one in the YouTube video, so you might want to watch it for that extra one ( https://www.youtube.com/watch?v=HP1V9VUDE9k). Enjoy!

image: finglobal.com

There are two kinds of youths in Nigeria: those who are already processing their emigration and those who are yet to.

If you want to know if I am already processing my emigration, you will have to read to the end to find out.

Nigeria is certainly a tough country to live in. It requires deliberate optimism and a fat salary to not feel sad about being stuck here. I have both and in today's post I want to share that optimism.

I earn more than I spend. That explains the fat salary. I spend less than I earn.

I like a quiet life, a simple life and a creative life. Luckily, I get plenty of that here in Nigeria. I am writing a novel about what life is really like in Nigeria. Staying here will help me write it well.

I try to look for opportunities that open up due to the pessimism around. Everyday I monitor the stock market. I am building an analysis dashboard on the real estate market -- which locations are experiencing price increase over the last decade and which are over-hyped.

I examine gaps that the mass exodus is opening up. I look for which gap I can profit from. Maybe I should start a course for those needing to prep their CV for emigration. Luckily, I am in a field that gets a lot of foreign love. I just need to help others attract some of that love.

If you've got ideas of how people like me can make better value out of their staying, do please share. While I am still in the "yet" group, let me maximize my stay.

Surprised to here from me? 

Special thanks to Dr Joseph who gave me a good encouragement to resume posting. And to Yinka and many others who left me messages to not give up or discontinue posting.

Truthfully, everyday I think about resuming my daily posts. The good part is I know that I have the capacity to do it, it is more of a desire to do it I am battling. Somehow, I get this thoughts that my daily posting is beginning to tend towards vanity and babbling and saying too much.

That battle still rages on. In the end, you'll experience the outcome.

Today, I have a special news to share with you.

I will be hosting a YouTube live session on investment and financial planning. You can indicate your interest to attend at https://www.meetup.com/Financial-Modeling-Financial-Planning-Meetup-Group/events/265264304/ 

The date, time and venue (online) details are:

• Wednesday 2 October, 2019
• 1:00pm - 2:00pm (Lagos time)
• YouTube live at https://www.youtube.com/watch?v=CEAA7ar6xfM (just go to that link on any of your devices at that time)

I plan to share very practical tips and my recent knowledge of the real estate market. It will also be interactive. You can ask me questions and see me reply. It's a live broadcast.

We also have a Telegram group for continuous learning and knowledge share: https://t.me/joinchat/Gfyf4VFBAOHYM3kdc98QHA 

Thanks.

Last week, I got a call from a past participant of our Power BI training regarding an issue he was battling. We provide after training support for all our training, which is one huge value most other training providers don't provide, especially those who don't have in-house dedicated trainers with deep consulting expertise.

He has system generated reports that look like the following:

Which when you bring into Power BI, still look like that.

How then do you turn them into this:

... but in Power BI.

With help from Matt Allington's amazing blog, I got what will do the magic.

We create Custom queries to grab and store the region and company before promoting the actual header row to being the header.

Click on the fx right beside the formula edit space to add a query step.

We then add two custom queries, one to grab the region and another to grab the company name.

The first is: 

= Record.Field(Source{[Item="Sheet1",Kind="Sheet"]}[Data]{0},"Column8")

Which is essentially telling Power Query to grab the first cell entry on the Column named Column8. For the part inside the Record.Field but before the {0}, just copy paste the last query in there.

We do another custom query to grab the company name.

Now that we have grabbed and stored those cell entries, we can now go back to the source table and do our normal header row promotion.

To get back to the source table, just do another add step but paste in it the exact query in the step before our first custom query.

What is left is to create Custom Columns with those saved values.

And that was it! Just move them to the beginning of the table.

Okay, that's not entirely true. That wasn't just all we did. He had many of those system generated files and already autosave them in a folder. So we needed to import the folder and have Power Query combine the files but first do these transformations before combining the files.

However, this good enough for today's post. Here's the Power BI practice file: https://urbizedge.blob.core.windows.net/urbizedge/Sample%20Data%20for%20Power%20Query%20custom%20cell%20entries%20grabbing.pbix 

Enjoy!