Charts for Dummies?

[Guest]

I really don't want to have to learn VBA - I know enough to make out what my Excel macros are talking about, but I just do not have enough time to learn all the ins and outs of charts and graphs programming.

However, my boss is not an accountant. He can read a financial statement, but I did a graph/chart thingy one day, showing one week of sales this year, vs same week last year. He really loved it, and it was far easier for him to "see" the data's values than just a bunch of numbers in rows and columns on a piece of paper.

My problem is that oh-so-nice 'thingy' took me a solid 8 hours to figure out, and I couldn't reproduce it today if I tried.

All I want is to pull our sales figures (or purchases, or gross profits) out of our General Ledger, and do some simple 2D graphs comparing whatever periods (this month to last, this yr to last, 5 yrs' history, etc.), or compare expenses to sales, etc..

I would prefer a REALLY simple "Wizard for Dummies" asking me what data range(s) I want to compare or chart, then give me a few options to choose how it will all look, etc., and it would even use the column headers for the labels.

By having a choice of charts/graphs available, I would be able to do different charts for different data, running certain charts for the managers' weekly meetings, and including others with the monthly statements packages.

Why does everything Microsoft have to be done at the programmers' level?

 

[JulieD]

Hi

depending on how your data is structure depends on how easy this is ...
basically to create a graph you need three things ... the headings (column
headings - these generally become the x axis of the graph), the categories
(row labels, these generally become the series lables - appear on the
legend) and the actual figures you're graphing

so if you want to graph the following:
A B C D E F
G
1 Jan Feb Mar Apr May June
2 Hats 10 20 15 5 3 2
3 Shoes 7 12 1 3 5
9
4 Gloves 20 5 4 8 6
7

you select from A1 to G4 as this incorporates the headings (row1), the
labels (column A) and the data B2:G4 and then press your F11 key - one
graph.

if you only want to graph the information for Jan you need to select
A1:B4 (to get the labels, headings & the data) - and now press F11 to
produce graph

if you only want to graph the information for Mar you need to select
A1:A4 (to get the labels) and D14 to get the headings & the data - and
now press F11 to produce graph

if you now want to ADD June's figures to the last graph select G1:G4
(headings & data) and copy it ... now go to your March graph and choose Edit
/ Paste - the values will now be added to the graph.

if you want to ADD last year's sales figures into the "this year's sales
figures" graph and these are on another sheet in the workbook and they have
the exact same structure but the headings are different then create the this
year's sales figures graph as per above, then select the headings, labels
and data from the other sheet and then choose edit / paste and this will
paste the last years sales figures into your graph
these values can also be added and removed & modified under chart / source
data - series tab

to change the graph type choose chart / chart type
to change the type for one series only, click on series, choose chart /
chart type & ensure Apply to Select is checked
to change the colour of any element in your chart - double click on it
to change the axis, right mouse click and choose format / axis
to add a chart title or axis headings chose chart / chart options

Hope this helps
Cheers
JulieD

I really don't want to have to learn VBA - I know enough to make out what

my Excel macros are talking about, but I just do not have enough time to
learn all the ins and outs of charts and graphs programming.

However, my boss is not an accountant. He can read a financial statement,

but I did a graph/chart thingy one day, showing one week of sales this year,
vs same week last year. He really loved it, and it was far easier for him
to "see" the data's values than just a bunch of numbers in rows and columns
on a piece of paper.

My problem is that oh-so-nice 'thingy' took me a solid 8 hours to figure

out, and I couldn't reproduce it today if I tried.

I would prefer a REALLY simple "Wizard for Dummies" asking me what data

range(s) I want to compare or chart, then give me a few options to choose
how it will all look, etc., and it would even use the column headers for the
labels.

By having a choice of charts/graphs available, I would be able to do

different charts for different data, running certain charts for the
managers' weekly meetings, and including others with the monthly statements
packages.

 

[bobf]

can you use the offset function to select the parts of
your spreadsheet that you want to chart ?

thanks, Bobf

 

[Jon Peltier]

Bob -

You can make dynamic ranges, and use these in the chart. Check out some
dynamic chart examples and lots of links here:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jon Peltier]

With that attitude, how will you ever become an expert programmer??
Never mind.

On my web site, I have some tutorials that might help you get started.
That section of the site is pretty much incomplete, but it will get you
to the point where you can begin to figure out things yourself.

http://peltiertech.com/Excel/ChartsHowTo/index.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______