Everyone, sooner or later, will have to deal with spreadsheets, one of the most complex and powerful tools that can be used on a computer in the workplace. This is not intended as a guide for beginners on spreadsheets, but intends to highlight four types of activities that you need to know how to do, to be good with programs like Excel. Microsoft Excel is certainly the most used program in the world to create tables and spreadsheets, but the same functions are also available on free suites such as LibreOffice and on Google Docs.
To be successful in office work, or at least to make life easier and to promote yourself better in hiring interviews, you must become good at using Excel (or an equivalent program such as LibreOffice and Google Docs ) and be able to do at least the following 4 activities .
READ ALSO: How to make calculations on Excel
1) Enter data quickly using forms or forms
Entering data into a spreadsheet is the starting point of any table analysis. All Excel beginners write data manually into cells, but they would be much faster to use a Form. The Form is a self-guided form for fast data entry into cells in order to write and press Enter to go to the next cell .
In Excel 2013 the form button was somewhat hidden.
To find it you have to press on the ribbon at the top and choose to customize it to add the modules button.
In other versions of Excel instead press the Office key, go to Excel Options and customize by adding the form.
Once you press the Form button, you can create a form (or a template, or a data entry mask) by writing the column headings.
In LibreOffice Calc you can enter data with the form by pressing on the menu Data-> Form .
Before using the Form, I need to create the column headers so that the compilation becomes guided and quick.
In Google Drive (Google Docs) forms are an object in themselves that allow you to create multiple-response surveys to share online.
You can create a form from the Create button or you can insert it (with the Insert menu) within a spreadsheet.
Help pages in Italian:
- For Libreoffice Calc
- Office Excel
- Google Spreadsheet
2) Perform calculations with functions and formulas
Once the data is entered into the cells, it is likely that an operation or calculation is required.
Functions and formulas must be written in the text box above and allow you to manipulate the data of a spreadsheet, perform simple mathematical operations, add numbers in a column, average or even work with real world objects such as dates or the money.
All calculation programs have their own set of functions, with some differences.
These pages in Italian contain all the formulas that can be performed on Excel, LibreOffice Calc and Google Drive.
Knowing them all by heart is impossible, but knowing what you use the most and what you need for your work is essential.
For example some basic operations are:
- The sum of the numbers in the cells: = SUM (A1; B1) adds two numbers while = SUM (A1: A5) is the sum of all cells from A1 to A5.
- = AVERAGE (A1; B1) is the average of two numbers; = AVERAGE (A1: A10) is the average of the numbers A1 to A10.
Each formula can also be found from the Insert - Formula menu.
You can also combine two functions and create a formula such as, for example, = ROUND ((SUM (A1: A10)); 0) to round the decimal places of the sum of the numbers A1 to A10.
On the functions, read: How to use formulas to count on Excel (COUNT and SUM)
3) Sort the data with filters and pivot tables
Once you have entered the raw data and performed all the calculations using the functions and formulas, you now need to create different ways of viewing them.
You can sort a series of data by column: press CTRL-A to select the entire sheet, go to the Data tab of the ribbon, press " Sort " and choose the column and the criterion which can be, for example, by number order.
Always from the Data menu you can also insert Filters, for example to exclude duplicate elements.
Pivot tables are advanced filters that are used to filter large lists of data that would otherwise be impossible to read.
Pivot tables allow you to take some pieces of data to summarize and see only a few of them, hiding the rest.
In Excel you create the Pivot table from the Insert menu while in LibreOffice Calc from the Data menu.
To find out more, there are guides in Italian for:
- filter data in Excel
- Pivot tables in Libreoffice
- pivot tables in Google Drive.
4) Create Macros for repetitive tasks
Working with Excel could be very boring because it often involves repetitive tasks.
To avoid doing the same job every day, you can create a macro and record the things you do so that the computer can do it on its own.
Macros are a strong point of Excel while in Libreoffice you have to be careful because some Macros may not be compatible with Microsoft Office.
In Excel, LibreOffice and Google Drive, it is possible to create more complex macros with programming scripts that would require some study.
Here are some resources to get started:
- Excel: Working with macros
- Excel Macro (In English)
- LibreOffice: First steps with macros
- Google Drive: Google Apps Script overview (English only).
All of these features are just the tip of the Excel iceberg and other programs. Using spreadsheets is another professionalism and a discipline in which you can immerse yourself very deeply.
READ ALSO: 6 most useful Excel functions to filter data and see graphs
