Contextures

Contextures News 20180731

Excel Build a Timeline

July 31, 2018

Automatically change currency symbol, project timeline, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

NOTE: You'll get the next newsletter on August 14th -- we're on the summer schedule now.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Currency Format

If you have customers in multiple countries, you might need to show product prices in different currencies. With Excel's conditional formatting, you can automatically change the number formatting, to show the correct currency symbol for the selected country - no macro needed.

currency selection list

  • On the Orders sheet, select a country from the drop down list in cell B1 (named Country)
  • On the Countries sheet, there is a lookup table (CountryLU), with country names, exchange rates, and currencies.
  • Cell G2 is named CurrSel, and it has a VLOOKUP formula that returns the currency for the selected country.
  • Then, back on the Orders sheet, a conditional formatting rule checks CurrSel, and sets the currency format based on that result.

currency VLOOKUP formula

Read the details in my blog post, and there is a short video that shows the steps. Get the sample file from my website - CF0003 – Conditional Formatting for Currency Symbol

Build a Timeline

If you know when a project needs to be completed, Excel can help you build a timeline, to show when each project step should start. You can build a timeline for business projects, but I had fun with a chicken dinner planner instead.

This project has 12 steps, but each step doesn't always depend on the previous step. I wanted to find a way to connect the steps in a specific order, while keeping the formulas consistent. If you have different formulas in a column, things can go horribly wrong!

So, I numbered the steps, then added a column (End Cell Links), where I typed the number of the connected step. An INDEX/MATCH formula in the next column calculates the end time, based on the connected step's start time.

=INDEX($B$10:$B$21,MATCH(I10,$D$10:$D$21,0))

build a timeline

Go to the chicken dinner planner page, to see the details, and to download the sample file. There is an online copy of the workbook on that page too - enter a dinner time, and chicken weight, to adjust the timeline. (I'm not sure if it will work in all web browsers.)

Excel Articles

Here are a few Excel articles that you might find useful or interesting.

Power BI - If you missed Microsoft's Business Applications Summit this month, watch the session videos online. Type "Excel" in the Search box, to see a list of those sessions, such as Building BI in Microsoft Excel, by my friend, Ken Puls, who teaches at Power Query Academy. (Level - Int/Adv)

Developers - Learn more about Excel's new extensions and addins, at the Excel Developer Conference in London, on October 18th, 2018. This is a non-profit event, so the price is low, and there is an early bird discount. Seats are limited, so if you're interested, sign up soon. (Level - Advanced)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Rainbows Rule

On the weekend, we celebrated Christmas in July - I didn't realize that it is an old tradition. We put up a tiny tree, then bought presents at the Dollar Store - the total was about $15. That's pretty cheap, compared to Christmas in December! The next day, we made this cheerful "Rainbows Rule" poster, using craft supplies that we got for Christmas. And we used scented markers, so it smells nice too!

weekly photo

That's it for this week! If you have any comments or questions, send me an email.

NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180731ctx.html
I'll also post any article updates or corrections there.

Debra Dalgleish
dsdalg @ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: August 1, 2018 10:47 AM