8 Basic Excel formulas for professionals
According to a Harvard Business Review, 750 million people work daily in Excel. On a survey to professionals working in offices, results suggests that we spend more than 10% of our working lives doing spreadsheets. The time rises to 30% for people working in finance, research and development.
Below I present some of the formulas and shortcuts that, in my opinion, are most useful for marketers, administrators and professionals in commercial areas.
1) Paste Special
It serves to select which elements copies and which do not. Also to avoid copying unwanted formats or formulas.
After clicking Ctrl • C to copy, press Ctrl • Alt • V to select what you want to copy. Then press Ctrl • E • S • V to paste those elements. - You can also use the drop-down menu from "Home".
2) Insert several rows
Adding rows one by one is not always as fast (and practical) when the list is large. Press Ctrl • Shift • + and select the number of rows you want to add, then right click.
3) Flash Fill
Flash Fill is one of the most efficient ways to save time when working in Excel. It allows to identify patterns to the spreadsheet to complete some cells, and fill them out automatically.
Press Ctrl • E, or go to the "Data" tab and activate the quick fill. If the option does not appear, go to "Advanced" and select the automatic quick fill from there.
4) Quick Sum
Instead of using the + sign or the formula = SUM, you can perform this operation in two seconds for an entire row or column. Press Ctrl • Shift • + • =
5) Remove Duplicates
Go to the "Data" tab and click on "Remove Duplicates". Excel will ask you in which column you want to apply the formula, and that's it.
6) View duplicate data - without removing them
To see the repeated data without deleting it: select the row or column of data and click on "Conditional Format". Then go to "Duplicate Values" and select how you want to see the duplicate data.
7) F4 for Fixing References
To work with absolute references, (I mean, to drag formulas WITHOUT a specific number changing) it is necessary to add a sign of $. For example $B$5 indicates that it will always be cell B5, and when applying automatic fillings that value will not be modified. To avoid using the $ sign, simply press F4.
8) Ctrl • Arrows
This combination will allow you to go to the last number in a matter of seconds, or scroll through the document without going through all the cells. It can also be used to select data faster.