Your 𝗘𝘃𝗲𝗻𝗶𝗻𝗴 𝗘𝘅𝗰𝗲𝗹𝗲𝗿𝗮𝘁𝗼𝗿 — one Excel tip to boost your financial modeling skills.

Tonight's Topic:

𝘂𝘀𝗶𝗻𝗴 𝗡𝗔() 𝗶𝗻 𝘆𝗼𝘂𝗿 𝗰𝗵𝗮𝗿𝘁𝘀

Ever wish you could show one line, but have part of it solid for "Actuals" and the other part dotted for "Forecast"?

You can do that using NA(), here's how:

1. First, for any chart, put your dates along the top

2. Then, create two lines below the dates:

-- Actuals

-- Forecast

For the Actuals formula, you can build this:

-- IF(the date at the top is an Actual, give me the number, else 𝗡𝗔() )

And for the Forecast, you can build this:

-- IF(the date at the top is a Forecast, give me the number, else 𝗡𝗔() )

You should end up with something that looks kind of like this:

Tag: Actual Actual Actual Forecast Forecast
Date: 1/31 2/28 3/31 4/30 5/31
Actual $100 $170 $180 # N/A # N/A
Forecast # N/A # N/A # N/A $190 $200

Now -- you can create a line chart by grabbing all 3 fields (Date, Actual, Forecast).

And in the data formatting window, you now have two formatting options (Actual or Forecast).

So, you can format Actuals to be a solid line, and Forecast to be a dotted line.

That's it!

Nice little trick to greatly improve the effectiveness of your visual aid.

𝗧𝗵𝗲 𝗻𝘂𝗺𝗯𝗲𝗿 𝗼𝗻𝗲 𝘁𝗵𝗶𝗻𝗴 𝘁𝗼 𝗿𝗲𝗺𝗲𝗺𝗯𝗲𝗿 𝗮𝗯𝗼𝘂𝘁 𝗺𝗼𝗱𝗲𝗹𝗶𝗻𝗴?

❌ It's not about how efficient, Excely, or fancy your file is.

✅ It's just about getting the job done.

I hope this helps you get there just a little faster.

—Chris

If and when the time is right, I offer refreshingly straightforward Financial Modeling Courses for FP&A and Private Equity Professionals that have been recognized all over the world.  Check them out if you're interested (if not, that's cool too 👍).  Just click here.