Home RSS feed

 

Excel sort

I recently created an explanation of how to automatically sort a list of data in Excel.

I wanted to present the technique as simply and effectively as possible. Pages like MrExcel use jpg images to show screenshots of Excel sheets. This works nicely, but I wanted something more usable and accessible. HTML tables are also frequently used around the internet, but often rather primitively.

I used a simple HTML table with border="1" and some cellpadding as a basis, and added CSS on top. I found that I needed the HTML border and cellpadding for the table to be readable without CSS. The CSS has two functions; to mimic the Excel look and to use colors for highlighting.

The HTML+CSS approach has both advantages and disadvantages compared to screenshots. Disadvantage: Difficult to show colored squares that span several cells, such as:

Spreadsheet screenshot

Advantages: Can show the formula of more than one cell at a time. The text can be selected and copied, etc. Instead of cell-spaning rectangles, I use different colors for the cell contents.

I wanted it to be possible to copy the whole table and paste it into Excel or OpenOffice. You can, but you have to delete the first column afterwards.

I ended up with two style sheets (Creative Commons license):

I am also wondering if I can do something more with the Excel functions. I could link to the function reference, like VLOOKUP. Or perhaps I should link to my translation page; VLOOKUP. The question is what language to translate to. Perhaps we could use Accept-Language from the HTTP header. For now, I just put a <span class="msexcel"> around the formulas to make them easily available to machine interpretation (e.g. Greasemonkey scripts).

After writing the page, I found a video presentation (Flash) of the same technique. They do not mention the +COUNTIF function however.

Tags: excel, css

Created on Tue, 07 Apr 2009 17:07

blog comments powered by Disqus