My boss wants me to prepare some spreadsheets with our financials but I’m on an Ubuntu Linux system and have no idea how to get started. Help!
First off, a confession: I have an MBA so it turns out that I really love working on spreadsheets. In fact, I used to get extra credit for my over-the-top spreadsheet designs when I was in business school. But just because I enjoy ’em doesn’t mean you have to do so too. 🙂
Turns out that the basics of all spreadsheets seem to be remarkably similar across programs, whether it’s that mainstay Microsoft Excel, Apple’s Numbers program or the cool pre-installed Linux alternative LibreOffice. In fact, If you haven’t poked around with Libre Office you’re going to be darn impressed to realize that you have an open source suite of programs on your Ubuntu system that offers 95% of all the functionality of Microsoft Office, but without the ridiculous price tag!
Spreadsheets are basically tiny databases. Each data point is known as a “cell” and cells can be related through “formula”. Every cell has a unique address too, a sort of x,y coordinate of row+column. A cell might be known as D3, for example, and a formula could repeat that value by simply stating it’s “=D3”. Want to add D3 and D4? “=D3+D4”. Easy.
But let’s just jump in and see… To start, launch LibreOffice Calc (which I’ll just call Calc from herein on)
This is pretty daunting, I admit. So many empty cells!
To make things a bit easier, I always start by zooming in, which you can do with the slider on the lower right or just click on the “100%” and it’ll reveal a window of zoom options:
This might not make a lot of difference on a small screen, but if you have a lot of screen real estate because you have a 50″ monitor, well, you’ll definitely want to maximize what you can see to make your life easier and task faster. I just bump it up to 150%. Makes me feel less stressed about building the spreadsheet.
The first step in any spreadsheet construction project is data entry. No big surprise there, but I recommend you just type in all the values and don’t worry if they look correct. Typically, for example, currency will be shown incorrectly. No worries, we’ll fix that in a moment.
Here I’ve specified two column titles and entered both text and currency values. By default, it’s pretty sloppy.
To start fixing things up, let’s select both column title cells (click & drag: in my Ubuntu theme this makes ’em orange) then change the background color of those two cells by clicking on the paint bucket and choosing a light gray:
Actually, that orange is pretty nice, but… no. Not really very professional to have it in bright orange, right? 🙂
Now let’s fix up those currency values. Same idea: Click and drag to select the column (and you can select empty cells that will be ready to show currency values once you enter data there) then this time click on the “$” icon on the right side of the toolbar, as shown:
Notice that the column heads are also centered and in bold (I did both while I had the two cells selected earlier, they’re both directly accessible from the toolbar too).
I’m not done with my data input, but let’s jump directly to adding a formula. Spreadsheets have tons of built in functions and one of the most useful is to sum up a set of values with SUM. All formulas are entered by simply ensuring that the first character is “=”, followed by the specifics of the formula itself. Like this:
LibreOffice Calc is trying to be helpful with its pop-up tip but I’m going to use the cell addresses and the “:” notation to indicate a range. The result:
What’s really cool about this is that if you’re editing that particular cell it will now show you the formula – and highlight the referenced cells as appropriate (see below) – and let you edit or tweak it as desired, but if you have a different cell selected, it’ll show the result of the calculation.
I’ve done the rest of my data input and made it a two column chart for fun too. This just means my formula needs to be a smidge more complicated, but now you can see how it all fits together, I hope?
I’ve obviously applied the proper formats, centered and used bold to get the best possible results.
Finally, with all that done, here’s my spreadsheet ready to share with the boss:
And a grand total of $19,956.51. Not too bad. Of course, where spreadsheets are fun are when you change one value and watch the change bubble through all the formulas and dependencies, but that’s another story for another post. This should, however, get you started building a basic spreadsheet in LibreOffice Calc and then formatting it to look attractive. Good luck!
Pro Tip: I’ve been writing about Linux since the very beginning of the Unix spin-off operating system. Please check out my Linux help pages for lots more tutorials and how-to articles while you’re here!