6 LibreOffice Calc options that deal with jobs you’d usually use Excel for

LibreOffice Calc is usually dismissed as a spreadsheet that is tremendous for primary quantity crunching, however one thing you’d rapidly outgrow as quickly as your work will get critical. As somebody who’s used Excel for many years, I perceive that view. Excel is extremely highly effective, and in any case this time, I nonetheless discover myself reaching for it after I must get one thing finished.

But fairly than take that assumption at face worth, I put Calc—a part of the free, open-source LibreOffice suite—by means of six duties I’d usually affiliate with Excel. I wasn’t anticipating it to switch Excel, however I used to be shocked by how far it may go.

Work backward from a goal with Goal Seek

Calc can remedy for the quantity you want

Goal Seek is a kind of Excel instruments I take advantage of after I know the end result I need, however do not know which enter will get me there. It’s doing a little fairly subtle work behind the scenes, so I needed to see whether or not Calc may deal with it too.

I arrange a espresso store mannequin with 5,000 cups bought monthly, a $1.80 price per cup, $7,500 in mounted prices, and a goal revenue of $10,000. I needed Calc to work out how a lot I’d must cost per cup to hit that concentrate on, taking all these prices under consideration. I opened Tools > Goal Seek, set Monthly revenue (B7) because the method cell, $10,000 because the goal, and Price per cup (B3) because the variable cell. Calc labored backward and returned $5.30.

That was begin. Calc had dealt with a job I’d often flip to Excel for, and the method was simply as simple.

Summarize a dataset with pivot tables

Calc can flip rows of knowledge right into a helpful abstract

If Calc could not create pivot tables, I’d in all probability cease testing it there. I take advantage of them so typically for summarizing massive datasets that I would not think about a spreadsheet with out them a critical different.

Calc dealt with this simply. I created a small gross sales dataset and used Insert > Pivot Table to summarize earnings by salesperson and product, then rearranged the fields to have a look at the identical information by area and product.

If you’ve got used Excel’s PivotTables, the method feels acquainted. Excel offers you extra choices for customizing and interacting with them, however Calc did what I wanted.

Run a correct regression evaluation

Calc goes nicely past primary charts and formulation

This was one of many exams that basically put the “Calc is simply for easy spreadsheets” thought to the take a look at.

I created a dataset containing promoting spend and gross sales, then went to Data > Statistics > Regression. I chosen promoting spend because the X variable and gross sales because the Y variable, and Calc generated a full regression report on a brand new sheet.

It included R-squared, ANOVA, coefficients, P-values, normal errors, and confidence intervals. In different phrases, this wasn’t a trendline with a quantity slapped on a chart. Calc produced a correct statistical evaluation.

I ran the identical information by means of Excel and received primarily the identical outcomes. The solely distinction was that I needed to allow Excel’s Analysis ToolPak first.

Use fashionable lookup and array formulation

Calc handles the spreadsheet tips you already know

Next, I needed to strip issues again and take a look at the one factor spreadsheets basically do: formulation. Microsoft has added loads of shiny new functions to Excel lately, making it simpler to deal with all the things from lookups to filtering and sorting.

It’s comprehensible that open-source spreadsheet software program can lag behind Microsoft right here. But now that the mud has settled, I questioned whether or not Calc had caught up with among the capabilities I now use just about day by day.

I examined XLOOKUP, FILTER, UNIQUE, and SORT. All 4 labored. FILTER, UNIQUE, and SORT had been significantly fascinating as a result of their outcomes spilled into neighboring cells, very like they do in Excel. When I intentionally blocked a part of a spill vary, Calc threw a spill error too.

That’s a fairly large deal for me. These have turn into on a regular basis spreadsheet instruments in my Excel workflow, and Calc dealt with all 4.

Automate repetitive work with macros

Calc can report and replay macros too

Macros are one other characteristic that may make a spreadsheet really feel like a small app fairly than a set of cells—one thing you would possibly assume solely Excel can do.

Calc helps macros and macro recording, too. Once I’d enabled the macro recording choice beneath Tools > Options > LibreOffice > Advanced, I may report a easy macro, run it, and have it enter textual content right into a cell routinely.

Of course, Excel’s VBA ecosystem is way deeper. If your spreadsheet automation relies upon closely on VBA, Excel might be nonetheless the software program you’d flip to. But Calc can automate repetitive duties, providing you with one other strategy to work past formulation and guide information entry.

Clean up textual content with common expressions

Calc places regex straight into Find & Replace

This was in all probability my favourite take a look at as a result of Calc did one thing Excel’s Find & Replace does not.

Don’t get me mistaken: Excel now has regex functions—REGEXEXTRACT, REGEXREPLACE, and REGEXTEST—so it may well completely deal with common expressions. It additionally has Power Query, which is a a lot better match for bigger or repeatable data-cleaning jobs.

But after I began poking round Calc’s regex choices, I used to be shocked to search out common expressions constructed straight into Find & Replace. Take a dataset containing names adopted by e-mail addresses. In Calc, I opened Find & Replace (Ctrl+H), checked Regular expressions, and used:

• Find: .*<([^>]+)>.*
• Replace: $1

Calc turned “John Smith ” into “[email protected].”

For a fast, one-off cleanup, that is actually handy. I did not want a helper column or one other instrument—I may clear up the present information proper the place it was.

Calc deserves extra credit score than the “primary spreadsheet” label suggests

I do not suppose the standard “Excel for superior work, Calc for primary spreadsheets” declare tells the entire story. Calc dealt with each take a look at I threw at it, together with some surprisingly substantial spreadsheet jobs. And for the regex cleanup, I truly most well-liked the way in which Calc dealt with it. I began this take a look at anticipating to search out the bounds of Calc. Instead, I discovered myself repeatedly considering, “Yep, it may well do this too.”