Excel gives your users quick editing and control options and shortcuts that can make working with spreadsheets faster. Tools such as pivot tables, autocomplete, conditional format, and waterfall charts are some of the tricks within the program. These functions of the Microsoft spreadsheet program are ideal for those who have a large volume of tables to edit. Check out ten Excel tips for getting your life ahead and see how to do them.
Excel formulas: the 68 most important functions of the program
Excel the Microsoft Office Spreadsheet Program Photo: Photo: Carolina Ochsendorf / dnetc
Want to buy a cell phone, TV and other discounted products? Meet the Compare dnetc
Pivot tables allow you to gather a large amount of data into a smaller table. By filtering the information, the most important data is gathered and gained more prominence. The tool is very useful as it creates an easy to understand table with less information, something that allows greater focus and concentration.
To use the tool, go to the "Insert" menu in the top bar of Excel. Then select the "PivotTable" option. A settings window will open, choose the cells you want to format in "Select a table or range" and finally choose between "Create new worksheet" or "Existing worksheet".
Pivot tables summarize the most important data in a few cells Photo: Reproduction / Gabrielle Ferreira
The tool acts as a pattern fill mode. Upon detecting a data pattern, the function automatically sends the following data to the blank gaps in the table. Its functionality is useful for sorting records and preventing users from performing repetitive fills, thereby gaining more agility in creating standardized tables.
Functionality should be enabled and working by default in Excel, but if not, it is very simple to enable it. Click on "Files", the first tab of the top bar. Then select "Options" in the bottom left corner of the screen. A new window will open, click "Advanced" and select the "Enable autocomplete for cell values" box.
Learn how to enable automatic data completion Photo: Playback / Gabrielle Ferreira
The primarily financial purpose functionality is widely used for stores and businesses, for example. It is possible, with the tool, to determine the predictions needed to achieve specific results. Such as, for example, the amount of products needed to be sold to achieve a final cash result.
To find the tool, go to the "Data" tab in the top bar. In the "Forecast" box, select the "Hypothesis Test" option. Then click on "Achieve Goal". Just select the cells you want to work with.
You can plan goals within Excel Photo: Reproduction / Gabrielle Ferreira
With conditional formatting it is possible to adhere value by using fill colors to specific data. The ideal function for highlighting the most important data in a spreadsheet and can separate them into groups of distinct or similar objects. For example, by defining a condition, one can select red color for negative values and green color for positive ones, and thus the system will automatically place the cell in the indicated format according to its assigned value.
To activate the tool just click on the desired cell, then click on "Conditional Formatting" within the "Styles" group. Already in the format option, choose "New Rule" to create a new format condition.
Format cells according to data entered in them Photo: Reproduction / Gabrielle Ferreira
Charts provide visual representations of the data entered within a spreadsheet. Functionality becomes very useful as it demonstrates, more clearly and didactically, the trends of values. Within the graph it is also possible to view the data in ascending and descending form.
To use the feature, select the cells you want to work with. Then go to the "Insert" tab and select the "Recommended Graphics" option. A new window will open, choose the "Waterfall Chart" template and click "Ok". A chart will be generated with the spreadsheet data.
Create charts within Excel Photo: Reproduction / Gabrielle Ferreira
The Soma function is one of the most basic within the program, but not all users know it and know how to use it. The tool used to add multiple values contained within a spreadsheet. It is ideal for those who want to make a control of spending and payments, because through the program, it is possible to do accounts automatically, without having to waste time gathering all the values.
To use the tool, simply enter the numeric values within the cells of the file. Then select the nearest blank cell, enter the function "SUM ('Start cell': 'End cell'). For example, we use the formula" SUM (B1: B10).
Sum up several values quickly and automatically Photo: Reproduction / Gabrielle Ferreira
7. Find out count of days
Within Excel it is possible to calculate the number of days between two different dates, since calculating long distances manually can be a complicated task. The ideal function for users who like to set up date tracking worksheets such as payment dates and birthdays.
To activate the simple tool. In the first cell, type the current date and, in the next cell, the end date to calculate. Then select the next blank cell and type the following function "DAYS ('end date cell' + 'current date cell)". For example, we use the formula "DIAS (C1; B1)". The result was in the next blank cell, "D1".
Within Excel it is possible to count how many days are left until the chosen dates. Photo: Reproduction / Gabrielle Ferreira
Excel has, as one of its shortcuts, the option to identify and delete cells that have the same dataset. The tool becomes very useful when reviewing the document for similar values or names and can automatically eliminate duplicates.
To remove repeated information, go to the "Data" tab at the top of the screen. Then select the cells you want to check and click the "Remove Duplicates" icon. A new window will open, check the checkboxes that will appear and select "Ok". A scan be made, erasing the duplicates.
Remove duplicate information in document Photo: Reproduction / Gabrielle Ferreira
It is also possible, with Excel, to create lists with predetermined values, conditioning which values can be inserted into a cell. The option is widely used within group-worked documents, in which several people have access and can edit the file. By conditioning the response, the possibility of error in entering information is eliminated because the cell does not accept the insertion of random data.
To select the allowed values, go to the "Data" tab in the upper program bar. Then select a cell or a set of cells and choose the "Data Validation" option. A settings window will open, select the "List" option. Then choose the allowed values and terms. A list will now be displayed within the cell, with the data options allowed.
Define what data can be entered in each cell Photo: Reproduction / Gabrielle Ferreira
One of the existing tools within the program is the option of sorting names and numbers in order, either ascending or descending. The ideal function for users who need to create spreadsheets in a sort, alphabetical or numerical order.
To sort cell terms alphabetically, select the data you want to organize. Then select under "Sort and Filter" the desired option. It is possible to classify from A to Z or from Z to A. The setting also applies to numerical order.
Sort cells by alphabetical or numerical order Photo: Reproduction / Gabrielle Ferreira
Via ComputerHoy and Pplware
Error in Excel: What can I do to solve? Comment on dnetc's Forum.
How to delete all blank lines in Excel at once