LibreOffice Calc Hacks

by Meemaw

I get Tech Republic's newsletter in my email every day. Sometimes it's nice to look through and see what's new or what's going on in the tech world. Being only a little geeky, I don't understand a lot of it, but I always stop and read the articles on MS Office hacks. I am a LibreOffice user. I even use it at work rather than MS Office, which is also on the office computer. When I see those articles, my first question is always, "how many of these work in LibreOffice?"

When I was reading recently, I came across just such an article. This Excel article appeared on December 4, 2017. As always, I wondered which would also work in LO, so let's look.

Number of sheets

When I open LO, and start a new spreadsheet in Calc, it always starts with one sheet. If your spreadsheet usually needs more than one sheet, you can add sheets as needed. You can also change the number initially provided. To change this setting go to Options > LibreOffice Calc > Defaults and change the default for new spreadsheets. (Remember that you have to have Calc open to find that section.) You can change the number to whatever you want, and the next time you open a new spreadsheet, you will have that many sheets.

Starting workbook

The article says, "If you often work with the same workbook, you can open it and launch Excel at the same time. Simply save the file in Excel's XLSTART folder. If you're using Windows 10, you can find the folder here: C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART" It goes on to give instructions on how to find that folder in Win10.

While I am sometimes up to changing a file/folder to get a desired result, I haven't found that type of folder in LO. If you find it, please let me know. I think a much simpler idea is to put a link to that spreadsheet on your desktop, and simply open it from there. I have links/shortcuts to several documents on my desktop at work (Win7 though), and it's very handy to open them from there. If you are someone who hates icons on your desktop (me at home on Linux), then it's also easy to open the LO startup window that has your recent files listed. If you use that document a lot, it should be listed right near the top.

Cursor movement

When you press Enter in your spreadsheet, the default (for LO Calc and Excel) is to move the cursor down one cell. If you want to move to the right, you can press Tab instead of Enter, or you can adjust this behavior as follows:

Go to Options > LibreOffice Calc > General, and choose the direction you want from the drop-down.

Template modifications

Excel bases all new workbooks on a template called Book.xltm. I'm sure Calc uses a template as well, but I can't find it… (if you know where it is, please tell me). In the article, "Modifications you make to Book.xltm are present in all new workbooks. Simply make the changes you want to persist in all new workbooks and then save the file appropriately as a template:

1. Click the File tab and choose Save As.
2. Enter book as the filename.
3. Choose Excel Macro-Enabled Workbook (*.xltm) from the dropdown.
4. Use the browsing bar above to select your local drive (probably C:) and then enter the path to XLSTART.

To use the modified version of Book.xltm, press Ctrl+N or click New on the Quick Access Toolbar (QAT). Excel will open a new workbook that reflects your template changes. If you want to make more modifications, be sure to open the Book.xltm template itself, as you would open any other file.
Be careful when modifying this template. All new workbooks will present these changes. In addition, if anything happens to the template, Excel will generate a new one, and it won't contain your customizations."

The best way to have a custom sheet you start up regularly is to create your own template, saved with a distinctive name.

Secondary startup folder

From the article, "Some tips rely on the built-in XLSTART folder. Anything you store in this folder will open automatically when you launch Excel. You can add a second startup location as follows:

1. Click the File tab and choose Options.
2. Select Advanced in the left pane.
3. In the General section, enter the path to the alternate startup folder.
4. Click OK."

This is to open several files at once, whatever is in the XLSTART folder, and whatever is in the second location designated. While it may be good in certain situations, I don't usually open a pile of files all at the same time. If I do, they aren't only Calc files. I do several documents for my board meeting every month at work, but they are usually a combination of Calc and Writer files. To do that, I open the file location where they are saved, then select them all and click open. (I don't think you can do that in MS Office anyway, because each type opens in its own program and won't open one type from the other program, meaning that if you are editing a Word file and need to look at an Excel file, you can't just go to the Open dialog in Word and open an Excel file.)

Text Wrap

1. In Styles & Formatting, right-click Default and choose Modify.
2. Choose the Alignment tab, and under Properties, check the box in front of Wrap text automatically

Make Text Wrap the default.

This default works in the current workbook only. Bear in mind that wrapping text will increase the height of that row.

Expanded formula bar

The formula bar's height (or depth) is adequate for most of us, but those who enter long expressions might benefit from more space. Fortunately, it's easy to achieve. Looking at the right end of the formula bar (see the red circles below), click on the down arrow (top) to expand it, then the up arrow (bottom) to reduce it again.

Recent documents list

When you open the list of recent documents, LibreOffice has defaulted to 25. I'll have to say that's enough for me, unless I have been searching for something particular. MS Office has made this one a bit easier to change, with a drop-down in Advanced Options that says "Show this number of recent Excel workbooks".

In LO, we need to open the Expert Configuration window, which is opened by going to Options > LibreOffice > Advanced and clicking on Expert Configuration. You will have a window much like the about:config tab in Firefox. BE CAREFUL!

I did, however, find the setting for the recents list. It is under org.openoffice.Office.Common. Double click the plus sign and look for History. Double click that and look for Pick List History. The settings at the right side should say PickListSize long 25. Double click the number and a window will appear to let you change that number. This will show recent documents from all sections, not just Calc. I changed mine to 20 at work.

Ruler measurement

LibreOffice (and Excel) ruler measurements default to inches. But inches won't always be appropriate. You can quickly change the measurement as follows: Go to Options > LibreOffice Calc > General and you can change to the units you want.

LibreOffice has been updated to Version 5.4.4.2. The other improvements can be reviewed here.

You have seen some easy ways to set defaults that work the way you work, but there are many more. Spend time in LibreOffice Options and you might find other easy changes you can make.