Help making line graph from date vs profits columns

[211]

My wife runs a small booth at the local Farmer's Market. I'm trying to get her organized a bit with her money as far as profits and expenses so she can see 'what her money is doing' and where it's going.

So far I have a worksheet with 5 columns:
-Date
-Cash Sales
-Credit/Debit Sales
-Expenses (lunch, coffee, booth fees or anything taken out of the 'till' that day)
-Total Sales

'Total Sales' is the sum of 'Cash and Debit/Credit Sales' minus 'Expenses'.

What I'd like to do is create a seperate graph which shows a trend in sales over the course of time. I.E. a line chart where data from the "Date" column is on the X-axis and "Total Sales" is on the Y-axis. The chart will expand as new dates are added and the Sales and Expenses cells are filled in.

I'm having a hard time creating this in a simple graph. Do I need to be working with a Pivot Table?

PS I want to keep this really simple for her. She's not savvy with excel and if she has to enter more than a few cells worth of data or fuss with tables it's likely she wont use it at all.

 

[Steve=True]

FYI - You may want to rename Total Sales to Net Sales. More appropriate in my mind, not that you asked.

Easiest way to do this is if you are in Excel 2007 or 2010, highlight your data and then click on Insert and then Table.

Then create your graph

If you make your data a table, as you add new data each month, the table will grab adjacent data into the table and also update your graph.

Hope this helps

 

[211]

I'm using office 2003 but if your advice still pertains then I may give that a try.

So far I've been able to get a graph to show up (not sure what I did originally to bork the data but...).
Basically by using the Ctrl key I selected a range of cells in the "Date" and "Total Sales" columns (the cells are mostly blank but preformatted- waiting for an entry to be made), clicked the chart button and created a standard line chart. This displayed the dates along the bottom of the graph with Sales in the Y-Axis.

I also created a second worksheet (basically a copy of the first) which will be used to tally profits for different market on a different day of the week (Farmers Mkt on Saturdays, Village Mkt on Wednesdays).

I copied the range of Date and Total Sales cells from that second sheet and added to the graph by Pasting as New Series. After some formatting I was able to get trends from both markets to appear in the single graph in a manner that seems to make sense.

So far this "works" and looks okay.
How will working from a table be different?
The data ranges I'm using to populate the graph are not in 'adjacent' cells (if that makes a difference) they are three columns over.

"Net Profits" makes sense. I'll change that at one point as well.

Thanks

 

[Steve=True]

I don't think tables are supported in 2003.

in 2007 tables automatically add rows and columns and copy the formulas when you add new data row or new months, columns.

To make your charts dynamic, you may have to us an offset formula.

some tutorials are available here:

Use Offset, Count and Index/Match in a name for your chart series.

Put this in a name. make a chart and change the series to the name you created.

This formula uses offset starting a the top point, then goes down to the end of the data using count.

=OFFSET('Using Offset Formula'!$G$6,0,0,COUNTA('Using Offset Formula'!$G$6:$G$14),1)

you can find more on chandoo.org search for dyanmic-chart-tutorial.xlsx