1. Use keyboard shortcuts
Following tricks help you to work smooth and fasting., shortcuts are the easiest way to speed up excel work.:
|CTRL + Arrow Keys||Quickly move around within your data. For example, CTRL + Down Arrow selects the last cell below the current cell that has data in it before it hits a blank cell.|
|F2||Begin editing the selected cell (just like double clicking the cell)|
|F4||Repeat the last action you took (e.g., applying formatting or deleting a row)|
|F12||The same as selecting Save As… from the File tab, but much quicker.|
|When Editing Formulas|
|F4||F4 will toggle the leading $ which makes the column and row parts of your references static (A1, $A$1, $A1, A$1)|
|F9||Also while editing formulas, pressing F9 will evaluate the selected part of your formula. This can be useful when error-checking your formulas.|
|TAB||Autocomplete the name of the function that matches what you’ve typed. For example, if you type “=CON” then press TAB, the formula will autocomplete to “=CONCATENATE(“.|
|Alt + Enter||Insert a line break into a cell without leaving formula editing mode.|
2. Conditional Formatting to view duplicate cells
Smart Tip: You can filter table columns by cell color. These two features together allow you to filter a table to show only duplicates.
3. Define Named Ranges
Named Ranges make it easier to refer to single cells or a range of cells in a formula by giving them a name. Simply select a cell or range of cells, click the “Name” box in the Formula tab and enter a name. Now, when you need to refer to the Named Range in a formula, just start typing its name and Excel will know which cell(s) you are referring to.
4. “New Window” on the “View” tab to look different sheets from the same file in separate windows
5. Create drop-down lists with Data Validation
It allow you to ensure that correct data is being entered into a spreadsheet, and can also alert you to incorrect data.
6. Quick Access Toolbar (QAT)
To add a feature to the QAT, right-click the item in the ribbon and click “Add to Quick Access Toolbar.”
Smart Tip: Add the “Paste Special” function to the QAT and you’ll be able to paste values or formats with two quick keystrokes (ALT + 1, then V).
7. Use INDEX–MATCH formulas
Smart Tip : Instead of VLOOKUP formulas with INDEX-MATCH
8. Employ Tables and Structured References
Tables and Structured References simplify formula writing. Similar to Named Ranges, columns of data will be named automatically by virtue of inclusion in a table. Not only does this keep your data clean and easy to understand, it’s a cinch to refer to data on other sheets, as well.
9. Use the Group feature to quickly show and hide columns or rows