**Start your bar (or column) charts from zero.**

To illustrate why you should do this, let me share a personal example.

Over the weekend, *the *Jon Peltier visited Wellington. He is staying with Jeff (who occasionally guest blogs on Chandoo.org). On Sunday, we all decided to hike up a small mountain near my house for a leisurely family picnic.

While on the top of the mountain, Jo (my wife) took a few pics of us three Excel geeks. As we were standing on a sloping mountain face this is how the pictures look:

Looking at the picture on left, you would confidently say that I am way shorter than other two. But picture on right tells a different story.

Of course, the reality is somewhere between two pictures. It is difficult to conclude who is tall, who is short just looking at the pics simply because the *baseline is sloping. *

**But we can’t have sloping baselines in Excel charts**

You are right we can’t. But we can still confuse people with an arbitrary axis start. Like below:

**The fix? always start your axis at zero for bar (or column) charts:**

Simple. Set the axis start point to zero (Select axis, press Ctrl+1, and from Axis options set minimum to 0).

So there you go. The shortest Excel charting tip ever, but still stands tall when it comes to telling great stories.

**More charting principles:**

If you are in the mood for some more charting theory and elegant methods, check out below links.

]]>**How to show selective sub-totals in Pivot Tables**

First instinct suggests that using Design ribbon > Sub-totals we can tell pivot table how we want the sub-totals.But this is like a master switch. It will turn off or on all sub-totals in the report. What we need is sub-totals only at one level of the report (in this case, sub-totals by department, but not by month).

The answer is simple.

- Click on any month label. This will select all months in the row label area.
- Right click > Field settings (you can also access this from Analyze Ribbon > Field Settings)
- In the “Subtotals & Filters” tab, choose “None”
- You are done.

Happy Pivoting. I am off to a short spin on my cycle before heading to work.

This is because **you are hiring a person for new temp role even before their current one ended**. See below picture.

So how to avoid making such hiring boo-boos.

Simple, using Excel of course.

There are a few ways to handle this problem.

- Using formulas to check if a person is hired twice in the same period
- Using conditional formatting to highlight such hires
- Using data validation to prevent such data entry

I made a video covering all these methods in detail. Check it out below or on Chandoo.org YouTube channel.

**Click here to download the workbook** showcased in the video. Play with various dates & names to test formulas, CF and data validation.

Learning few simple Excel best practices and techniques can save you a lot of time & money. So why wait, check out below links and become awesome in Excel.

- Advanced Excel skills & resources – learn the right way
- What is Conditional Formatting and how to use it? [Video]
- 5 tips to become conditional formatting rock star
- Introduction to SUMIFS
- MAX IF formula

While you are at this, subscribe to Chandoo.org YouTube channel. I have been uploading videos once a month. While this is not exactly a staggering pace, you are sure to be a rockstar at work if you just watch all the previous vids and stay tuned for more. **Check it out**.

- Introduction to Excel tables
- How to use structured referencing
- Tables and Relationships in Excel
- Using lookups and other formulas with Excel tables
- Simple way to get absolute references in Tables
- Customizing table styles for awesome usability

**While tables are super helpful, they do come with some limitations.** Today let’s examine one such unique problem and learn about an elegant solution.

Imagine you are the machine supervisor at Mighty Machine City Inc. Although your machines are mighty, sometimes they do fail. To keep track of which machines are under repair, you maintain a *repair log *in Excel. Since you heard Tables are mighty, you thought,

‘*Gee whiz, I might as well use tables to maintain the repair log. Chandoo says tables are sweet’*

So, your Repair log looks like this:

After a few days of tracking the repairs, you wanted to know if same machines are failing successively. For example, in above picture, you notice that MACH-0038 failed twice in a row starting with 11th of March. Same goes for few other rows.

You are the kind of person who frowns upon manually highlighting yellow color in cells to flag such successive failures. So you want to write a formula.

So you add a new column called **Same?** and want to fill it up with a simple relative reference formula to check with Machine in row 1 matches machine in row 2.

Here is the formula you used:

=[@[Machine ID]] = B6

*Note: *Your table starts with Row 5.

Excel automatically filled down the formula for all rows of the table, *because tables are awesome like that.*

You whistled your way to home that night.

Next day morning, as usual *Homer messed up something *and you had a new repair to log. So you went to the bottom of *fail *table and inserted a new row to add the failure details.

And you notice something unusual.

**The formula for Same? column is WRONG!!!**

As soon as you inserted a new row, Excel adjusted last row’s formula to something silly.

**Before:**Let’s say the last row formula reads =[@[Machine ID]]=B20**After:**The last but one row (as you now have an empty row)’s formula reads =[@[Machine ID]]=**B21**

Now that is clearly wrong!

**What is going on here?**

Your relative referencing worked ok, until the last row. At this stage, Excel *understood *the formula as **Current row value = value in first cell below table**

The part in green is what caused trouble. As soon as you add a new row to your table, *fist cell below table *is moved down. So Excel adjusted that reference alone.

**How to fix this problem?**

The usual method to fix this:

- Insert as many rows as you need and complete entering / pasting all data.
- Select the formula in very first cell.
- Fill it down all the way (you can double click on the bottom right corner of the cell)

**But that is soooo not awesome.**

You are right. This method is manual and error prone. It is the opposite of awesome.

**Problems when you delete too:** In fact, if you ever delete a row from your table, the formulas further down would show #REF! errors. So this method is not very effective in real life.

Instead of using cell address based references, like B6, if you use pure structured references, then Excel will automatically adjust them as your table grows or shrinks.

But how?

Simple, we can use OFFSET function along with @ references.

To get next machine ID, you can use

=OFFSET([@[Machine ID]],1,0)

So, to check if same machine failed twice in a row, use

=[@[Machine ID]]=OFFSET([@[Machine ID]],1,0)

As this uses no cell references, whenever you add / change / remove table rows, Excel automatically scales the formula.

**But I heard OFFSET is volatile / dangerous / RDX / %#$&@#?**

Unless your table has a 200k+ rows or you plan to set up 100s of columns like this, don’t bother. for small, day to day tables, there is no change in performance. If you really hate OFFSET, try talking about it during your next therapy session. Jokes aside, you can also use a longer INDEX based formula to get similar result, but that is semi-volatile too.

Here is one such INDEX based formula.

=INDEX([@[Machine ID]]:INDEX([Machine ID],COUNTA([Machine ID])), 2)

It sure is a mouthful. You can shorten it by using a named formula for INDEX([Machine ID],COUNTA([Machine ID])) portion or the whole thing. Again, I wouldn’t recommend the INDEX based approach over OFFSET for smaller data sets. For larger datasets, see if you can fix the problem at source (for example, modifying your SQL to get offset values in a separate column) or using Power Query to mash the data (more on this in a next post).

So there you go, an elegant and simple way to deal with the relative reference problem in tables.

You can use this approach to generate running numbers (1,2,3…) in a table column that grow / change / shrink based on your table. This can be very useful in many scenarios.

To get running numbers in a table column, just use:

=ROWS(fail[[#Headers],[Machine ID]]:[@[Machine ID]])-1

The pattern that you can use in any table goes like this:

=ROWS(Table_Name[[#Headers],[Col 1]]:[@[Col 1]])-1

You can then change the OFFSET based relative ref to INDEX like below.

=INDEX([Machine ID],[@Running]+1)

This method is 100% non-volatile, but does return #REF! error for the last row. So use it with IFERROR when nesting in other formulas.

**Click here to download an example workbook** showcasing all these techniques. Use the extra data to paste and test various methods.

The handful times when I had to use relative refs in a table, I resorted to cell refs (like B6 above). But this created too much headache further down. So I switched to OFFSET / INDEX approaches.

**What about you? **How do you write relative references in tables? Please share your stories & struggles in the comments section.

If you are relatively free and want some relaxed reading then check out below related reference links.

- An intro to Excel relative references, structured references
- INDEX formula, OFFSET formula, ROWS formula

Happy learning.

]]>You need to analyze this and prepare a cash flow model.

**Let’s learn how to answer such open ended questions using various analysis techniques in Excel**.

*The case in this article is based on a forum question by Proteus.*

Most business case problems will have following three kinds of parameters

- Fixed Inputs – for example opening stock of chairs & tables, book value of these items
- Variables – Number of chairs and tables sold every month (or year), profit expectation
- Assumptions – We will be able to sell off all the items (ie no write offs), Profit per unit and book value per unit doesn’t change over time

*Of course, these three categories can overlap*. Use your experience and industry knowledge to define what items belong where.

Of course, you can consider everything to be a variable in your model. This will give maximum flexibility, but comes with a lot of cost. Your model becomes complicated and can take a lot of time to develop. It might be overkill, so identify a few constants (fixed inputs).

Once your model is in Excel, all input cells can be edited. So *technically *all are variables.

The next step is to identify outputs. In this case, we can calculate three kinds of outputs.

- Number of chairs & tables sold by month
- Revenue by month
- Profit by month

We can add an optional output – *visualization of the results.*

This is where we figure out the business rules and calculation logic to arrive at outputs from inputs.

Let’s define formulas for each output

- Units sold per month = this year’s portion of total stock / 12
- Revenue per month = units sold per month * book value per unit
- Profit per month = units per month * profit per unit

Set up the input area like below. The orange cells contain user inputs. Gray ones have calculated values.

Everything in the above picture is self-explanatory, so let’s move on to output section.

*Note: *If your business problem is complex, you need to setup dedicated worksheets for each type of input (fixed, variable and assumption). This will let you play with various combinations and control outputs in a better way.

The tricky part is figuring out units of chairs & tables sold per month. Once we have these numbers, calculating revenue & profit per month is easy.

**Let’s run the outputs for 60 months.** Although your initial estimates suggest that all stock will be sold in first 3 years, this allows you to monitor cash flows over 5 years, should there be a change in the inputs.

Let’s say month numbers are in column G, from G6 to G65.

Given,

- the month number in G6
- Yearly chair volume in range C$19:C$23

*Refer to inputs picture in above section for cell references.*

We can calculate number of chairs sold in that month using below formula (call it formula 1)

- Units of chairs per month (cell H6) =INDEX(C$19:C$23,INT(($G6-1)/12)+1)/12

**How does this formula work?**

Simple, we pick the volume for year represented by month using INDEX formula. To calculate year from month (G6), we use simple arithmetic: INT(($G6-1)/12) + 1

Once yearly volume is picked, we just divide it by 12 to get monthly volume (ie units sold per month).

Notice the mixed referencing style used, this will help you drag and reuse the same formula for calculating table volume.

The calculated volume figures go in to columns H & I.

Now that we know units sold per month, calculating remaining three outputs is easy.

- Revenue of chairs (cell J6) = H6 * C$8
*(Note: C$8 has the book value per chair)* - Profit of chairs (cell L6) = H6 * C$11
- Total Profit (cell N6) = L6 + M6

**Refer to below diagram to see sample results along with formula numbers.**

Our initial model is a simple formula driven tabulation of results. **But what if you want to see profit flow by different scenarios?** May be the initial yearly forecasts by marketing department are too optimistic and you want to see what happens if we sell fewer chairs in first year.

Let’s **assume we have 10 such scenarios** and for each scenario, you want to define below inputs:

- Profit per unit
- Yearly breakdown of volumes for 5 years

This means, we have a total of 12 inputs per scenario (6 for tables and 6 for chairs)

Now that we have scenarios to define some of our inputs, let’s plug in scenario number in to input section, as shown below.

This is when we unleash the beast – **Data Tables**. Using data tables, we can quickly calculate total monthly profit for each of the 10 + 1 scenarios.

**Set up an empty scenario grid** as shown below.

Make sure first column refers to the monthly total profit calculated in column N (N6:N65) in our initial model. Once such a grid is setup, use below steps to calculate profit under each scenario.

- Select entire grid including first column (referenced one) and headers.
- Go to Data > What if analysis > Data tables
- Select Row input cell and point to scenario name in input area (cell C25 in my model).
- Click ok.
- Wham!!! Excel calculates profit for each of the 11 scenarios for all 60 months (total 660 values calculated before you could say six sixty )

While scenario based modeling is good, it presents a new challenge. How do you make sense of all this new data? Simple, make a chart.

There are many ways to visualize this data. Here is one:

I have visualized only first 5 scenarios (original + 4 more). You can change this depending on what each scenario represents.

Related: **Introduction to Data Tables in Excel**

Now we all know that no business sells same volume every month. You will have a few high months and few low ones. So **how to add monthly variations to the model?**

Let’s say you have monthly % splits for Relaxer and Boca Top defined in range as shown below:

We can plug this new information in to our model by altering formula 1 (units per month). Everything else will work nicely once formula 1 is fixed.

Here is the new formula 1 (units per month). Figuring out how it works is your homework.

=INDEX(C$19:C$23,INT(($G6-1)/12)+1) * INDEX(C$30:C$41,MOD($G6-1,12)+1)

Please note:

- C$19:C$23 has yearly volume for Relaxer
- G6 has month number
- C$30:C$41 has monthly % split for Relaxer

Once you alter formula 1, you can see how it effects the cash flow (revenues & profits per month) over 5 years.

You can buy a broomstick from Quality Broomstick Supplies in Diagon Alley before you can *accurately* figure out how much you will sell each year. It is almost impossible.

But our entire model depends on this input. **What if we don’t know the yearly volumes?**

May be we can assume first month volume & monthly variations (as defined in Model 2 above) and figure out yearly volumes. Since first month volume is a variable, we can alter it to see what kind of cash flow it would produce.

**Something like this:**

Let’s say we know first month volumes for Relaxer & Boca Top – Cells C43& D43 respectively.

We can calculate forecast in a few ways:

- We can calculate yearly volume by multiplying Q21 with first month’s percentage (as defined in Model 2)
- We can calculate successive month volumes by increasing / decreasing first month’s volume by monthly % changes (this requires new inputs)
- We can simulate monthly volumes by randomly varying first month’s number while following some sort of monthly split pattern

2 & 3 require new inputs or data tables to be set up. Since we have already beaten this problem to death, let’s just stick to approach 1.

We calculate yearly volumes by using simple formulas like this:

- Year 1 (cell C19) =MIN(C43/C28, C$6) (C43 has first month volume and C28 has first month %)
- Year 2 on wards: =MIN(C$19,C$6-SUM(C$19:C19))

Note: C6 has total stock of Relaxer. We can only sell if there is any stock left. If first month volume is too high, then we may end up selling out quickly.

Once these volumes are calculated, we just visualize results (monthly profit columns L & M) in a line chart.

**If you link inputs C43 & D43 with two separate scrollbar form controls,** you can play with them in the chart and quickly analyze the results. *Now that is pretty cool.*

*Related:* Introduction to Excel form controls | Using scrollbar form control in Excel Models

**Click here to download Excel Workbook **containing all the models discussed so far. Play with them or create your own models to analyze the problem. Learn and flourish.

* If you like this,* you are going to love my upcoming new course –

**Can you find all the 5 hidden cells in this workbook?**

First step. Download this workbook. There are five worksheets, each containing one egg. You need to find the hidden **cell** in each worksheet.

**One rule:**

- You should see the word cell spelled out clearly anywhere on the spreadsheet grid.

**Post your hunt results in the comments**. Post the cell address where you found the results along with process used to find in comments. Hunt them all before they rot.

Good luck.

Go thru previous year hunts. Be warned though, they are highly addictive.

]]>- How many employees are on leave during Easter holidays (14th of April to 28th of April)?
- How many employees are on approved vacation during Easter holidays?
- How many employees in "Team ninja" are on approved leave during Easter holidays? Assume team employee numbers are in named range
**ninja**

**Let’s say you are HR manager at Egg Co. and you are looking at the vacation plans of your team.**

Easter is your busiest time and it would be a bummer if a majority of your staff are on leave during the Easter season (14th of April to 28th of April, 2017). So you want to know how many people are on leave. This is how your data (table name: * lvs*) looks:

** Click here to download the sample file**.

You want to answer below three questions:

- How many employees are on leave during Easter holidays (14th of April to 28th of April)?
- How many employees are on approved vacation during Easter holidays?
- How many employees in “Team ninja” are on approved leave during Easter holidays? Assume team employee numbers are in named range
**ninja**

For first question, assume that any employees whose leave is *pending *will be approved.

Also, assume that Easter season start & end dates are in cells P4 & P5 respectively.

You can use formulas, pivot tables, power pivot measures, VBA or pixie dust to solve the problem. If using pivot table approach, just explain how you would solve in words. For other methods, please post your solution in the comments.

**Go ahead and post your questions. **

**Want some hints..?**

What is an Easter themed homework without some clues? So here we go

- Between formula in Excel
- Check if your two ranges of dates overlap
- Range lookup in Excel
- Count unique values subject to conditions

All the best. The weekend forecast is blue skies and light winds. Finally, we will be checking out walking trials in Trelissick park.

]]>