Smooth Powerpoint with Zoom

This tip will add a wow element to your presentation 😎. It will give you a way to connect multiple topics together. Let me show you first.

Step 1: Divide your presentation into sections using

Step 2: Add the zoom icons (mini slides)

Step 3: Edit the icons and setting from the new zoom tab

Here you go a wow element to your presentation 😎.

Excel Functions INDEX and XMATCH

One of Excel most useful functions that is mostly used to look up values is INDEX and XMATCH. Although they are separate functions but they are sisters.

=INDEX (array, row_num, [col_num], [area_num])

=XMATCH (lookup_value, lookup_array, return_array, [match_mode], [search_mode])

XMATCH returns a number of cell order where the look up value. INDEX on the other hand returns value of a given number of an array. So XMATCH feed INDEX and you get your look up. Remember this, the array in INDEX is your answer range. It can perform more complex functions than XLOOKUP. or does it 🤔. An example for that can be shown:

Tell me if it works for you. Or it doesn’t 😅. Let’s discuss.

Print Customized 1000 Envelopes to Customers By One Click

If you ever had to send hundreds of invitation litters to your customer, you know how time-consuming 😖 to prepare litters with name and title for each! After all it’s not your job 😩, It’s just something you need to do 😑! I feel you man.. I feel you *hug*. Good news is there is a built in feature in word that can make your life easy 😃.

First you need to have your list in excel file with all the information like: name, title, address, … etc.

Then in Microsoft word, go to tab mailing > select recipients and choose your file…

Lastly, insert fields from Insert Merge Fields.

You can preview your fields by clicking preview. You can edit size and font as normal text.

To print simply select range and click Finish & Merge.

You can use this for, envelopes, certificates, thank you litters etc. Literally for thousands names in one template. Cool right 😎. How long did it take you before let me know.

Don’t Ask What If? Ask What IFS?

One new super useful function of excel is IFS 💪🏻. Yes, no more hideous nested IF 🤢. And it goes like this:

=IFS(Condition1, Value if True1, Condition2, Value if True2, Condition3, Value if True3, …)

Quite self explanatory really! but there is the catch 🙄! it only tests if conditions are true “what if” you have a condition not specified ones! you’ll get the #N/A right into your face.

Don’t you worry I got you covered. Add a final condition that is always true as following

… , 1 , Value if else)

Note: True = 1 , False = 0

Thats it 😊. And here a bonus, now Excel has maxifs and minifs function like the lovely sumifs and countifs of before 😎.

Excel To The Next Level (Macros)

VBA (Visual Basic for Application) macros can take your Excel work to next level. Imagine you have a raw data with some variations on the input like, extra spaces, useless number within text and it’s provided to you in text file. You need to clean it up and do your calculation on it. EVERY … SINGLE … DAY😅! Macros can do this monkey job 🐵 for you.

But first! you need to add the developer ribbon tab. in Mac (Excel>Preferences>Ribbon and Tool Bar) in windows (File>Options>Excel Options>Customize Ribbon)

The easiest way to learn is by starting recording a macro! You click record, do the job you want to do, then stop recording.

Edit it by clicking on the macros icon.

Change parameters here and there. Improve your script gradually by searching the web. I would recommend sites for you like stackoverflow but why limit yourself! Google it! Whatever job you have in mind, chances are someone already has done it before 😁.

One more thing, When saving the file, you are going to receive a warning 😅😣

You got to save the file in macro-enabled format, which is (.xlsm) .

Fiddle with it and enjoy.

Universal Office Tip That Will Increase Your Productivity

This one is simple and quick. You just need to know it once and it is about how to find any functionality in Microsoft office application? In two steps

• First, look at the ribbon tab titles and guess under which title does the function you are looking for fall?
• Second, look at the subtitles at the bottom of that tab. Viola that’s it.

Lets take and example, in Excel where do you find Define Range Name? 1) My guess it’s either under data tab or formula. 2) In data tab I have subtitles (Get External Data, Connection, Data Types, Sort and Filter, Data Tools, Forecast, Outline), I find it doesn’t fall under any of those subtitles, then I go to formula tab and I found Define Names as subtitle right infront of my face 😅.

Powerpoint Top Tip

If you ask me about one advise about powerpoint I’ll say one word “master slide”. It will make your life easier. Basically it is like the template where any effect reflects on the rest of the presentation. you can add logo, change color, and font. You can access it from View > Slide Master.

And to exit from New tab of Slide Master > Close

An important note though, to have a consistent result use the specified boxes for text; write the titles in titles box not just in any drop text box. This way if you make any changes in master slide it’ll be reflected on the while presentation.

So, what is it you are struggling with? Let me know if I can help.

Excel New Dynamic Array

Ok guess that huge! This going to change Excel as we know it. It’s called Excel Dynamic array, which is a new way Excel processes formula. So what is it?

Simply now you can get an output as multiple cells that would “spill” out from one cell. Like of you have 1000 record of cities and want to know unique values. Just in one single shot. Here you go:

= UNIQUE(B2:B44)

Viola that’s it.

Moreover there is a function called SORT, so you can sort this output.

= SORT(UNIQUE(B2:B44))

However, you need to be carefull that below cells are empty because the array will “SPILL” into it.

If not you’ll get a #SPILL! error

Now, Are you thinking what I am thinking? Yes Dynamic lists 😍. But this for another day 🙄

Excel Pivot Table

If you don’t know it, well!, You gotta 😁. It’s one of those things you just need to know. Simply it works by highlighting your table then go to Insert > Pivot Table.

Now you’ll see on the right pane 4 rectangles below the list of you table heading. You basically drag and drop the columns and rows titles at first . As for the value part you can add any title that has values in all rows for counting purpose. There are different functions you can use ranging from counting to summing. Fiddle with it and and I’m sure you’ll get the hang of it.

Excel – Filters on Multiple Tables in One Sheet

If you are an Excel head and you use filters in your table to lookup data, you might get upset because you only can have one table per sheet to filter. Well not anymore! If you define your tables, well, as tables then you can have more than one table with perk of filter for each table in one sheet.

Tip: remove table style to maintain your styling 

and here you go …