Stuff I Learned This Year: Excel Edition
I like to think of myself as rather proficient in using Microsoft Excel.1. I use it for everything from keeping simple lists to advanced data analysis. I love pivot tables and conditional formatting and even have a favourite Excel function2 The other day at work I taught a bunch of colleagues, who are all very well versed in the use of Excel3 that you can copy something from one cell down a whole column by double clicking on the bottom right corner of the cell you want to copy. Most of them knew that you can grab that bottom right corner and drag it down as far as you’d like to copy, but they were all suitable stunned with the double clicking trick – which comes in especially handy if you have hundreds or thousands of rows of data – that’s a lot of scrolling if you are using the drag method instead.
Which brings me to the new thing I learned about Excel. It has a limitation that I’ve never run into before, but which is now an issue for me. Specifically, that limit is the number of rows you can have in a single worksheet. That number: 1,048,576 4,5. And I learned this as a particular set of data that I’m working with had more than a million rows of data! Our makeshift solution is to have multiple worksheets in a workbook, though now that we have almost filled our our *second* worksheet, it really slows down the old laptop!
Clearly, the next thing I have on my “things to learn this year” list is database management!
- One of the things that I do in the statistics course that I teach over at the Justice League is make sure that everyone who takes my class can use Excel properly – it’s probably one of the most useful thing they use in the course, to be honest [↩]
- CONCATENATE. Mostly because the word is always. I probably use “Text to columns” more often, but I love to say “concatenate”! [↩]
- Including one who I’d say is the best Excel user I know. [↩]
- Source: https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 [↩]
- It also has a limit of 16,384 columns, but I haven’t run up against that particular limitation yet. [↩]