I got an e-mail this weekend asking following question:
How do I represent many very large and very small data values together in a MS Excel graph?
There are multiple ways to do this depending on which data you want to show your users.
Split the axis in two parts
Let’s start of making a table with some data that we want to visualize.
Most of the values in the graph are almost invisible. This makes it hard to read our data. How can we fix this?
Start by decreasing the chart’s plot area. So we can make room for a second graph.
Click on the area around the graph and press CTRL+D . This will make a duplicate on top of the current excel chart.
Resize and align both charts. The second chart comes above the first plot area. Don’t forget to delete the column names from second chart.
Select y-axis. Go to “axis options” Under the section “bound” you lower the maximum range to that the small values on the graph become visible. Then take the second y-axis and increase the minimum. Delete the x-axis from the second graph.
Now go to Insert -> shapes and pick a rectangular shape.
Now use this triangle to cover up the line between the graph. Then go to settings and give the rectangle a white filling.
Plot with a logarithmic scale
This is a more scientific approach to the same problem. But this makes it less easy to comprehend for the general public.
First select the y-axis -> right mouse button -> format axis.
This will give the following result.
Do you have a different approach to this problem. Let me know in the comments.