Excel Magic Trick 349: Automatic Dynamic Chart for Last Nine Weeks of Data

See how to create a chart for the last 9 weeks of data the automatically changes every Monday. See the Excel features:
1)DATE function
2)YEAR function
3)MONTH function
4)DAY function
5)WEEKDAY function
6)INDEX function
7)MATCH function
8)TEXT function
9)Concatenation using the join symbol ampersand &
10)Formatting as façade
11)Date formatting and Date as serial number
12)Date custom formatting
13)Bar Chart
14)Removing chart junk
15)Varying the colors on chart bars
16)Adding data labels

Category:

Science & Technology

Tags:

• Excel
• Dynamic
• Automatic
• Last
• Nine
• Week
• Chart
• DATE
• WEEKDAY
• functions
• Bar
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[meltdownman1]

Sorry that I am late on this...So here is what I have:

Daily input on one sheet: dates entered in 1st column and data input into next 30 cells across. These cells feed into formulas on the same row (about 60 cells further across). Then indvidual charts are created on 20 separate sheets from the values obtained from the formulas. Problem: how to update the charts as I enter data every day but maintain the same fixed range of dates (~3 years) . Do u have a vid that shows how this can be done? Thx

[ExcelIsFun]

Try posting question to THE best Excel question site:

mrexcel[dot]com/forum

[Laydjo1]

@ExcelIsFun

Thanks for posting this. I've been following your posts on youtube and man, i say your the best when it comes to explaining all these complicated steps. I'm so glad stumbled upon one of your videos about a year ago. From then on I've been a fan...

[ExcelIsFun]

You are welcome. I am glad that the videos!

[retrorokkit]

Video & Trick of the year!!!!!! I updated all my reports and charts with this and saved myself hours of work. My boss thinks I'm amazing! If he only knew! lol. I can't thank you enough for your hard work teaching us these awesome tricks, functions, etc...

[ExcelIsFun]

You are welcome!

[meltdownman1]

NOTE: I am using EXCEL 2003...sorry but my company is a bit slow to get on board.

Thank you very much.

[ExcelIsFun]

The easiest way is with a Pivot Table with Dates Grouped by Week and Month. See this video and look for the part that shows how to group by dates:

Excel Basics #20: Data Analysis Pivot Tables PivotTables

Pivot Tables are by far the easiest way to accomplish your gaol.

[LotfyKozman]

Mr. Excel. If I have a record contains daily sales for one year period. How can I reach to a formula that can sum each week of each month together.

[ExcelIsFun]

I can't think of a good way...

When in doubt, post question to Mr Excel Message Board. I have a video about that site at excelisfun chennel.