Creating A Custom List In Excel; An Interesting Example.

If you are an average or above average Excel user, you will be very familiar with Excel autofill. A common example is you typing numbers 1 and 2, then dragging to have Excel do to 10 for you. Or having January and February, then dragging to have Excel do the other months of the year for you.

What if I tell you that you can create your own list. A list of states in Nigeria which you can autofill whenever you need to recreate them. Or a list of your products; you simply type the first two and drag to create the rest (autofill). It even helps you with sorting in the order you've created the list. This can be super useful for companies that use codes for aspects of their operations -- base station address code, branch code, product code and so on. Caution: The custom list has a quite limiting size limit. 

So here is the example of how I created a custom list of states in Nigeria, arranged alphabetical order.

First, I have the states typed out in Excel, in the order I want it (alphabetical order).

And we are half-way done to having it permanently in Excel as an autofill-able list.

Go to File, Options, Advanced, and Edit Custom Lists.

In the Custom List Dialog box that comes up, import the list of states you already have typed into Excel.

And you are done.

Now in a new Excel file or Sheet, type in the first two states, select them and drag down. Excel will start autofilling the other states.

Congrats! Now you've created a custom list. No more typing out states in Nigeria, just type the first (or first two) and have Excel autofill the rest for you.

Time Saving Use Of Excel's Advanced Filters In Working On Your Business Data

Every time you apply filter to a dataset in Excel and then copy out the filtered data, you could have saved yourself time and some mouse-strokes by using advanced filter. And despite the name, advanced filter, it is surprisingly easy to use.

As an example, I have the fictitious sales record for Dhormino's Pizza for June 5, 2016. They sell 16 Pizza types: Meatzaa, Extravaganza , BBQ Chicken, Hot Veggie, BBQ Philly Steak, Chicken Feast, Chicken Suya, Chicken Legend, Beef Suya, Margarita, Italiano, Pepperoni Suya, Veggie Supreme, Hot Pepperoni Feast, Chicken Bali and Pepperoni Feast.

They got a new country manager this March, and one of his strategies is to have four brand managers owning 4 different baskets of the pizza types, like the FMCG companies do. Two of those managers are Michael and Mary. They are to ensure that the brands (pizza types) they own are performing well. They are given the latitude and resources to promote them using whatever promotional mix they want. 

As the company's main business data analyst, you pull out daily sales records and extract the ones for each brand manager. With advanced filter, it will be a piece of cake. You might even hook it to a recorded macro and at a click of button all is done.

Continuing with the example, let's see how to extract for Michael and Mary.

Step 1 is to write out the pizza types Michael and Mary manage, separately. And to help advanced filter know where to look for those pizza names, give them a header that matches that in the sales table. See the screenshot above for what I mean.

Step 2 is to go to Michael's sheet (another Excel sheet where you want to put Michael's). Launch advanced filter from there. It is under Data menu, about the middle of the menu tools.

Set it to "Copy to another location"

Provide the sales data range as the "list range", and Michael's pizza types with the header as the "Criteria range".

Finally, for the "Copy to", go to Michael's sheet and select where the result should be displayed. In this example, I select cell A1.

And voila! We get the results.

Very easy and cool.

And there is more to advanced filter. How about if we have a more detailed sales transaction data that captures sales value for each sales transaction, and we would like to extract sales transactions that generated more that 15,000 naira. That too is very easy with advanced filter.

Just follow me. Below is the snapshot of the detailed sales transaction data.

We have specified that we want greater than 15,000 naira transactions only.

The steps are just like previous ones. Launch the advanced filter and provide both the "List range" and "Criteria range"

Specify where the results should be displayed.

And voila, again! It's done!

Now you should be an expert, like me, at using advanced filter. 

How To Set Conditional Formatting To Highlight An Entire Record Row Based On Value In One Field

Let's say you have started a side business. You sell about 21 different products. You've hired a trusted hand to manage the business but he is not Excel savvy. So you decided to set up some basic templates to handle invoicing, accounting and inventory management.

We will focus on the inventory management template you set up. It documents the stock of the different products you have -- the quantity in stock, the cost price and number of days they can be stored for. There is just one more thing you want to add. You want the template to highlight in orange products that are due for restocking. And your restocking rule is to not have less than 40 of any product. So once a product quantity drops below 40, it is marked for restocking.

How do you do that with Excel? 

You use conditional formatting. And in a creative way because you want it to not only highlight the quantities that are less than 40 but the entire record line for the products with quantities less than 40.

Form this:

To this:

And here are the steps to achieving it.

Highlight the entire records without the header. Go to Home menu, conditional formatting and click on new rule.

Select "Use a formula .." in the formatting rule dialog box.
In the formula box that shows, press = and select the first record in the quantity row. It will come as $B$3 (a dollar sign before the column alphabet and the row number). In our case we don't want the formula to drag to other columns (like cost, storage days and product columns) so you will leave the dollar sign before the column alphabet. But we do want it to drag downwards to other rows, so it would do each row in our table; for that we remove the dollar sign in front of the row number.

Final formula will look like this: =$B3 less than 40<40 40="" font="">
Got it?

Then you set the formatting to be displayed.

And that is all! Click on OK.

You should now see the result.

And that is how you set conditional formatting to highlight an entire row based on value in just one field.