How to display large amounts of data in Excel?

Question

Hi everyone!

I have a 15 minute presentation to give for my first post-university job interview on Monday (which is nerve-wracking enough!) but I am now facing a dilemma as to how to present the data for the presentation.

There are over 6000 pieces of data in each column that I have to analyse and demonstrate correlations. I would normally do this in a Scatter graph or Line Graph but as you can imagine this just looks like a big smudge with this amount of data!

Does anyone have any idea on how else to display this in chart form? Or how to minimise data?

Thank you so much!

Answer 1

Pivot Tables and Pivot Charts may be the answer to your problem. This tool is very good at organizing summary reports and charts from large amounts of data. You don't have to create a bunch of complicated formulae as it is done for you.



Click in your data, click on the Insert tab, click on Pivot Table down arrow and choose either the Pivot Table or Pivot Chart. You may even want to use both options to fully show your data.

Answer 2

Given the amount of data I would consider representing the best fit as a clearly defined line, and then plot the scatter plot not as individual data points but as a light colored, semi-transparent area plot. As you say, the amount will smudge any points you attempt to put on such a plot anyway. This way you can still present the data visually.



I generally avoid Excel for the plotting job and use Matlab for that. Excel is great for organizing the data in spreadsheets, but the graphing tools are... Unsatisfactory in my opinion. If Matlab was unavailable I would use Octave, which is open source and free.



In fact I would say keep the spreadsheet, but look into using Excel in conjunction with either tool. If you are familiar with LaTeX and the tikz/pgf package I would suggest using Excel for the data source, Matlab for the treatment of said data and generation of the plot which you could then process through tikz/pgf for generation of the final result. Tikz/pgf can generate stunning publication quality graphics, but as it is more low-level than most people are comfortable with it may prove excessive if you are unfamiliar with LaTeX.

If you ARE familiar with it, this script will export from Matlab to tikz: http://www.mathworks.com/matlabcentral/fileexchange/22022

Answer 3

lets see... you have 6 columns and 500 rows = 3000 elements say each element is oh say 6 bytes = 18K of data plus formating. Put it in a table which will add 28 bytes per column. See following example redgreenblue cinnamon3.14159 Washington data bytes in my example 3+5+4+8+7+10 = 37 = about 6 bytes per entry 28 bytes formatting = 77% overhead figure 65 bytes per row = 32k of data it would seem that the less formatting, the better. However, some browsers will load the entire table prior to displaying the 1st row. To allow for this you would probably wish to split the displayed data into say 10 tables of 50 rows each. The first 50 rows will appear faster within those browsers. You might just put the first 50 rows in one table and the remainder in a 2nd table. 500 rows is MANY screens of data. As such, this is a fairly strange request. How sure are you that you (or the customer) actually wants it done this way?