Every company, every student and possibly every western household uses it or has used it: Spreadsheet software. The most famous ones include Microsoft Excel, Google Sheets and Libreoffice Calc however, there are over 40 different types of spreadsheet software. The tools, calculating power and sheer ease of administration these software make this software essential for every business. This article will try to argument which software might be the best pick for you and some key tricks to upgrade those spreadsheet skills.
Working alone, or working (simultaneously) together?
I trust every student who reads this article has either used Google Sheets or Microsoft Excel at least once. As of this moment all students can get Excel for free (just google it) whilst Sheets has always been Free. Even though a lot of other free spreadsheet software exist, this article will focus on these two because of the popularity they have in business. So let’s look at some key advantages of both software.
Excel remains one of the trusted aids in business: superior calculating power, a larger selection of templates, better text and cell formatting, a greater selection of charts and better layout features. As we will see further on in the article the Solver function is one of the key reasons why Excel is above Sheets. However, when you want to work together Excel falls apart. Sure, a lot of businesses have a shared drive which allows people to work on the same file but there loads of limitations and people can not see each others progress. If you want to work together with Excel you better sit next each other otherwise it just won’t work.
On the other hand Sheets was made for collaboration. The cloud software has the same standards as all Google software: working together at the same time is the basis. Up to 50 people can edit and comment on the same file at the same time whilst also being able to see each others progress. It is exactly this last point which makes Sheets so valuable. Furthermore there are two other points where Sheets beats Excel: Efficient support and collaboration with other platforms. Sheets has entire pages by google which simply state the available formulas, how to use them and even offering extra tips. Entire constructs with data sharing between different spreadsheets, documents and even other types of documents such as forms or maps is also easy with Sheets.
In short, if you require heavy calculating power to solve very complex linear programming situations, you have over 2.000.000 cells of data or you want templates or a lot of customising in your spreadsheets choose Excel. If you aim for collaboration, ease of use, data imports from a lot of different files or want to use the general advantages of cloud software you choose Sheets.
Tips to upgrade your spreadsheets skills
Solver
As mentioned before in this article the Solver function is one of the key functions in both Excel and Sheets, even though the uses in Sheets are limited. Solver is an add-on that has to be activated manually which has the use to solve linear programming problems or other similar projects such as a production planning. It uses variables and limitations to optimise a certain objective function. The original Solver included in every version of Excel has its limits as well, to upgrade it you could look at alternatives such as Opensolver which is much clearer and has a more capable way of handling calculations.
Array formulas
Yet another way of working more efficient in spreadsheets is working with arrays. They enable you to more efficiently calculate things and even enable you to calculate things that you cannot do without arrays. An example of this is shown in the following example: The formula below, with the {} being the standard to represent an array, is the same as the formula underneath it.
{=SUM(C2:C11*D2:D11)}
=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11)
As you can see it is a lot less work, especially if the amount of cells used for the formula significantly increase. But that is not the only use of arrays they also allow you to:
- Find the location of the maximum value in a range
- Sum only numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary.
- Sum every nth value in a range of values.
Slicers
The last tip this article provides is often used in simplistic KPI dashboards or when people are trying to limit the data input for their data analysis. A simple way to limit the data shown in graphs is to use slicers. There are multiple versions of slicers and by setting relationships between graphs of the same data source you can use a single slicers for your entire dashboard. Simply said slicers show all the key variables you use in your data and enable you to limit them. For example you could use a timeline slicer to only show data of February and March.
Pick your favourite type of spreadsheet software and experiment, only by googling your way out of problems, trying new things and failing a lot of times can you become a power user of your favourite spreadsheet software. And in the end, you can show your fellow colleagues that you are an expert in spreadsheets.
0 Comments