Wolf-mother, where you been? You look so worn, so thin You're a taker, devils-maker Let me hear you sing, hey-ya hey-ya Wolf-father, at the door You don't smile anymore You're a drifter, shape-shifter Let me see you run, hey-ya hey-ya "Wolf" - First Aid Kit
The soundtrack to my quarantine has been mostly First Aid Kit and The Highwomen. Nearly every night, whether on purpose or by the subtle prod of the YouTube algorithm, I end up on this house concert and let the harmonies wash over me. Most of my other routines are off kilter these days (how could they not be?), but little quarantine habits are getting me through. These are the lyrics that came to my mind when I sat down to write today.
This week, I faced a spreadsheet challenge that fit squarely within the mission of the TDAA blog mission, and when I finished the exercise, I knew that I needed to blog about it. This was a “shape shifting” quest if I’ve ever seen one! I was handed a spreadsheet export from the platform, Monday.com, and needed to sort it into sortable, useable, shareable data. But how?
I commend Monday.com for even offering an export button – that is pretty darn cool and probably not easy to do as a web developer (though, what do I know?)… we built a similar feature at work to download some Salesforce pages into spreadsheet format and I think they were a doozie. Having this function made it possible for the organization to take this project management data and be able to manipulate it and share it with people who don’t use Monday.com – however, getting to that point required some good, old-fashioned spreadsheet elbow grease. The kind of work that can take 5 minutes or 5 tedious, frustrating, agonizing hours. I prefer 5 minutes, don’t you?
Caution: wide load
The first problem with the export was extremely wide columns that contained lengthy explanations. The export did not “wrap” the text (aka continue the text on a new line, in the same cell), so the columns were very very very wide, so much that you could not see the whole thing on one computer screen. When I dragged the scroll bar to the right, then I moved on to the next column, and could not access the “resize” tool to drag the column over and make it more narrow and easy to read.
Here’s how I fixed it: I used the “zoom” buttons in the bottom, right corner of my screen. I zoomed the spreadsheet way, way, way out – so that each column was so small that I couldn’t read the text, but at least I could see the left border and the right border! Then, I could use the “resize” dragging tool to scoot the borders over. Next, I selected all of the data and clicked the “wrap text” button so that I could see the full response (see .gif below for an example). From here, I went back to my normal zoom. Ahhh, so much better already!
Repeating repeating tables tables
The next problem was that the same header was repeated multiple times in the sheet, one for each region. Under the first header, a few rows of data. Then, new header, and more data. Mercury, Venus, Mars, etc. This looks nice on a website, but it doesn’t work well in a spreadsheet, especially if you want to summarize, sort, or filter the data.
Here’s how I fixed it: I added a new column for “Planet.” For each of the groups of data, I typed that planet on the appropriate row. So as I went down the list, I wrote “Mercury, Mercury, Mercury, Mercury, Venus, Venus, Venus, Venus, Venus, Venus” etc. Now, instead of indicating the planet by looking at the header, I could just look at the data in that row. If you had a large data set, this would be irritating (and there are probably ways to do automate this task) but for everyday spreadsheet users, it was no big deal. Then, I went through and deleted all of the headers. Ah, now we have all of the data in one, nice grid.
Rainbows are for windows, not for spreadsheets
Have you read about the “rainbow hunts” that neighborhoods are creating for kiddos during Shelter in Place? So quaint! And all the more reason to keep your rainbows in windows, not spreadsheets.
The next problem was that the Monday.com export had some extreme colors and shading that made it hard for me to parse the data. Also, it was less obvious what each color symbolized; whereas online, there was a little key and you could hover over the cell to learn more. We needed to take a big ‘ole eraser to the color palette.
Here’s how I fixed it: I selected all of the data and selected “Clear Formatting.” (Caveat: if the cells were shaded because of “conditional formatting,” I would have needed to clear out those rules first). But in this case, I knew that there were no rules, just some silly color coding!
Won’t you dance “width” me?
The last problem in the spreadsheet was wacky row and column widths. Some columns were too wide for a short answer (date or name) and some rows had extra blank space (I had resized a column, but the row never changed). It is very annoying to re-size columns and rows by hand, and also very imprecise. Why not let Excel do the work for you? An easy way to quickly resize columns or rows is to doubleclick at the border and Excel will “snap” to fit. However, this doesn’t work for going through lots of rows and columns.
Here’s how I fixed it: First, I selected the data. Then, I went to the “Cells” section of the toolbar and found the “Format” menu. Then, I clicked “Autofit Row Height” and “Autofit Column Width.” This helped quite a lot! Finally, I manually adjusted a few columns (especially the ones with lengthy paragraph explanations) so that I could maximize space on the page.
From chaos to order, the data are alright
From a downloaded file with weird fonts and titles, wacky colors, no wrapped text, superwide columns, and repeating header rows, I was able to create a useable, sortable, shareable, printable, filterable, not-make-you-want-to-throw-your-computer-out-the-window spreadsheet. And you know what the best part is? You can too!