1

I have an excel table with dates and according bank account balances. Now I add a graph and get the following:

enter image description here

The graph should have a timeline on the x-axis - that works. But I want the data to be shown as follows:

enter image description here

So that I can see account balance mapped to time. Unfortunately Excel connects the dots with a line - I don't want that. How can I achieve the second graph?

Thanks!

  • use a bar chart instead of line chart. – Máté Juhász Feb 24 '17 at 12:16
3

To get the effect you want you will need to use a normal bar graph and change some of the options. Here is a tutorial you can follow. I've edited to make it easier to understand for less skilled excel users. Here is the final result after following the tutorial.

enter image description here


Chart

  1. Select both data without the headers
  2. Go to the tab "Insert"
  3. Click on the dropdown list "Insert collumn or bar chart"
  4. Click on "Clustered Collumn"

Clustered Collumn

  1. Then click on a bar and make sure its selected (it will have blue dots on the edges.
  2. Click on the "Plus sign" hover over axes click on the arrow and then more options.

enter image description here

  1. Now select the bars again and an option menu should appear on the right
  2. Make the "Gap Width" 0%

enter image description here


Data points format

To make the dates align.

  1. Click on one of the dates in the graph to select them
  2. Open the menu. See step 5 - 6 from Chart.
  3. Click on one of the dates to select them again.
  4. Now in the option menu select the option "On Tick marks"
  5. At the dropdown "Tick Marks" change the Major type to "Inside"

enter image description here

| improve this answer | |
  • With this type of graph, the x-axis is not a timeline anymore but simly the data points in the table. What I want is to have a timeline on the x-axis to really see balance change over time – Mathias Bader Feb 24 '17 at 15:21
  • @MathiasBader How do you mean? When I change my data to match your it looks 100% exactly like how you want it. here take a look imgur.com/a/59j6p – RamonRobben Feb 24 '17 at 15:29
  • I either get a chart where each data point has equal width (which I don't want), or I get a chart where the datapoints correspond to the dates, but then there will be a lot of empty space between the bars – Mathias Bader Feb 25 '17 at 19:58
  • Look at the chart in my question: The entries in the table for dates are not the same as the text on the x-axis – Mathias Bader Feb 25 '17 at 19:59
  • @MathiasBader I get what you mean. That is also an option. I'll add it to my answer. I'll try to find the english version aswell. – RamonRobben Feb 25 '17 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.