- 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.

]]>- Dump several columns of data in the template file.
**Hide a particular set of columns**(these are not together, so must be done one at a time or with CTRL+selection)- Save and publish the file.

After doing this manually for last few fortnights, today I wanted to automate the column hide process. I was about to write a VBA macro to clone the hide settings from one workbook to another. But then I thought, may be paste special can be of use.

**And what do you know. It does exactly that.**

- Copy a row of cells in original report, doesn’t matter which ones
- Paste special > column widths (ALT+ESW) on the new report
- Any hidden columns in original will be set to ‘0’ width, thus becoming hidden in new report.
- Bingo!

**Here is a quick demo** of this in action. Check it out and apply next time you are doing something tedious like this.

There you go. Paste prevents painful problems.

**Other ways Paste Special saves the day**

Paste special is one of the top time saving features in Excel. Here are few of my favorite paste special tricks.

- Format faster with paste special & double click
- Remove data validation rules with paste special
- Speed up chart formatting (yes, you read it right) with paste special
- Convert numbers stored as text with paste special
- More awesome paste special tips

**What is your favorite Paste Special moment?**

Let’s get personal. What is your favorite paste special moment? Share it in the comments.

]]>I will try this experiment with kids during school term holidays around Easter. But first, I wanted to try the simulation in Excel.

Take a look at the final simulation.

**Pendulum pattern – Lissajous curve**

The patterns generated by sand pendulum are nothing but **Lissajous curves**. We can generate these curves in Excel by using below equations.

- X = A * SIN(a*t + d)
- Y = B * SIN(b*t)

where

- A & B refer to amplitude (the length pendulum travels before reversing its direction) along X & Y axes
- a & b refer to height of pendulum along X & Y axes (if these are same you get a simple pendulum and thus a straight line pattern)
- d refers to degrees of shift (its a bit complex to explain here, but read about phase shift on Lissajous curve Wikipedia page)
- t refers to radians (degrees).

**But this will generate a smooth line, how to show sand?**

Simple, after computing each pair of (X, Y) **we add a small random noise** to them. This creates an impression of sand falling from pendulum on to a surface and bouncing off. Let’s define our new equations as,

- X = A * SIN(a*t + d) + jx * rand()
- Y = B * SIN(b*t) + jy *rand()

Where jx & jy are jitter fractions (very small numbers, lest the dots will be too away from original points)

**What if the pendulum never stops?**

Since we are simulating the movement of a pendulum in Excel, we can choose to have a never stopping pendulum (ie a simple gravity pendulum). In this case A & B values never change.

In real life, A&B will reduce with each oscillation until the pendulum comes to a stop (because there is air drag, friction and other forces at play too).

Enough physics & maths. Let’s take a look at the chart & VBA behind this simulation.

- Set up 3 columns, one with t values starting from 0 and increasing by 0.05 per cell, next two with X & Y values.
- Leave the X&Y values blank. We will use VBA to fill these.
- Let’s say we use 5000 (x,y) values to plot the chart. This gives enough variation. You can alter the number of rows later.
- Create a scatter plot with smoothed line using these 5000 (x,y) values.
- Using VBA, iterate thru column t and calculate X & Y values for each t value using above equations.
- Update the chart by using doEvents after every
*n*calculations. Try n=25 first and change as you see fit. Updating the chart after every calculation slows down the animation significantly. - Change A, B, a, b, d, jx, jy and air drag factors to generate different curves.

Here is the VBA code used for the animation.

```
Sub animate()
Dim xVal As Double, yVal As Double, jx As Single, jy As Single
Dim t As Range
Dim a1 As Single, drag As Single
Dim a2 As Double, b2 As Double, d As Double
Dim updateScreen As Integer
Dim tVals As Range
a1 = 1
drag = Range("air.drag")
a2 = [a.2]
b2 = [b.2]
jx = [j.x]
jy = [j.y]
d = WorksheetFunction.pi() / Range("d")
Set tVals = Range("t.vals")
Range(tVals.Offset(, 1), tVals.Offset(, 2)).ClearContents
Range("done") = "drawing..."
For Each t In tVals
xVal = a1 * Sin(t * a2 + d) + jx * Rnd()
yVal = a1 * Sin(t * b2) + jy * Rnd()
t.Offset(, 1).Value = xVal
t.Offset(, 2).Value = yVal
'update screen after every 25 times this loop has run
updateScreen = IIf(updateScreen = 25, 0, updateScreen + 1)
If updateScreen = 0 Then DoEvents
'Reduce A & B values by using drag
a1 = a1 * (1 - drag)
Next t
Range("done") = "done"
End Sub
```

**Click here to download the Pendulum Sand patterns workbook**. Play with the animate & random pattern buttons to see some cool patterns.

This is a cool way to teach kids the awesome pattern power of simple things in life. Have you done this experiment? If not, give it a try. If you think playing with sand is too messy, try the Excel workbook.

Also check out: 3D dancing pendulums post to see some cool & clever animations. Huge collection of spreadsheet tools & simulations for teachers.

]]>The idea is that when you have a direct, simple way to do something, you should use it.

**Nose and pivot tables… how are they connected?**

We are coming to the point. Recently, learneagerly, one of our forum users asked a question about how to transform (reshape) a set of data in Excel.

**Marc L**, one of our Excel ninjas, posted an awesome VBA script to do that.

Later in the day, I chipped in and shared a formula approach to transform the data.

*I suggest checking out both approaches for learning more about VBA & INDEX formula respectively.*

After posting my answer, I got thinking… May be there is a more direct way to reshape the data.

Why, yes, there is. You can use **Pivot Tables**.

Here is a snapshot of raw data and expected output.

As you can see, we have two columns of data and we need to extract *n *(here it is 6) items from first column, then *n *from second column and lay them out in output. We repeat this until we run out of the data.

The first step is to add two extra columns to your raw data. Let’s call them Running & Repeat.

**Running:**with numbers 1 thru 6 and repeat the pattern (just auto fill or copy paste)**Repeat:**with 6 cells of 1, 6 cells of 2… and repeat this pattern (auto-fill)

**But what if I want a different n**

Even better. use formulas. Let’s say your data starts from H6:I6

**Running:**=MOD(ROWS($I$6:I6)-1,)+1**n****Repeat:**=IF(J6=1,SUM(K5,1),K5)

Related: Using ROWS() to generate running numbers in Excel.

Now that we have these extra columns, select all the data (2 columns of data + 2 extra columns we just added) and insert a pivot table.

**Set up the report by,**

- Adding Repeat & Running to row labels area (in that order)
- Add Col A & Col B to values area.
- Move the
**? values**to row labels area (by dragging it) - Position
**? values**between Repeat & Running row labels. - Your pivot report’s last column will have the transformed data.
- Viola, nose pointed!

**Here is the example workbook**. Examine the pivot table & formulas in Running & Repeat columns to learn more.

* Are you struggling to find your nose or worse still, twisting your arm on the way?* If so, check out our Excel school program. We have awesome online lessons, beautiful explanations, powerful techniques and easy to understand downloads. It won’t be long before you are smelling roses.

**Check out our Excel School online class & join today**.

**Pivot Tables and Power Query are my go to tools** for almost all kinds of reshaping problems. Often, I indulge in INDEX formulas or a bit of VBA. For example, just a few days ago, I had to split first 100,000 digits of Pi ? in chunks of 3 digits, 3 digits and 14 digits in a pattern. As the data is too long, loading it Excel cell was impractical. Loading it in to multiple lines with each having * x *digits was impractical (as I may need to split them in another pattern). So I used a simple VBA script to zap the data and get what I need.

*In case you are curious: *I made a chart to celebrate the Pi day (14th of March) with our community on Twitter.

But when I am not splitting irrational stuff, I usually rely on Pivot tables or PQ.

* What about you? *How do you reshape your data? Please share your approaches and tips in the comments section.