Excel is a powerful tool for data analyzing and mathematical work. Discover the most useful excel tricks to save you time, stress, and elbow grease here.
Excel is one of the best-selling pieces of software ever made, with over 750 million users around the world. People might not think a spreadsheet is very exciting but that’s because most of those 750 million people don’t know enough about it. Let’s look at seven Excel tricks that will make you love using it and seem like a wizard to your coworkers.
- Inserting Rows or Columns
You can insert a row or column by clicking on the header to highlight the entire row or column and using the Insert command on the Edit menu or right-click menu. If you need to insert several, you could repeat those steps several times but it can be done in one step by highlighting the number of rows or columns you want to insert. The insert command will insert the same number that you highlighted.
- Selecting Data in Your Excel Sheet
There are several shortcuts for selecting data within your spreadsheet:
- Click the header at the top left corner to select all cells.
- Hold the shift key and use the arrow keys on your keyboard to select multiple cells.
- Hold the shift key and double-click the dot in the lower right of the cell to select all values in a column.
- Using Excel’s Autofill Feature
The dot in the lower right of the cell can also be used to autofill cells in the column or row. Click and drag the dot to fill the adjacent cells. If you want to fill the cells with certain numbers, enter a few in the series, highlight them all, and drag the autofill control.
For example, if you want to fill 1 to 31 for days of the month, enter 1, 2, and 3 in the first three cells. Highlight them and drag the cell down to fill the rest.
- Automatically Fix Typos
Excel’s Autocorrect feature lets you fix common typos or set up shortcuts for things you enter over and over.
For example, if you often type “teh” you could set up an Autocorrect entry to replace teh with the. Or if you work for ACME Plumbing and Heating, you could set up an entry to replace APH with the full name so you only need to type APH whenever you want to enter it in a cell.
- Limit Cell Entries with Data Validation
Excel’s Data Validation feature lets you restrict the values entered in a cell to certain numbers or one of a list of items. For example, if you have a list of employee names you could set up Data Validation to only allow those names in a cell. If someone tries to enter something else, Excel won’t accept it.
- Highlight Information with Conditional Formatting
Excel’s Conditional Formatting tools let you highlight values in a row or column based on many criteria, including:
- Higher or lower than a certain number
- Between (or not between) two numbers
- Minimum or maximum value
- Duplicate values
Or you can use an Excel formula to determine what values get highlighted.
- Combine Data with VLOOKUP
Excel’s VLOOKUP function is one of its most powerful but underused features. VLOOKUP lets you combine data from two different tables, two worksheets in the same workbook, or two separate spreadsheets. As long as both tables have some kind of unique value in common, you can use VLOOKUP.
VLOOKUP is a powerful way to combine data but it does have its limitations. If you need to go beyond what it can do, you can use macros or custom programming to work with Excel spreadsheets. For example, you could read data from Excel in C# and create a custom macro to do more complicated manipulation.
These seven tricks will get you started but they only scratch the surface. You could spend months or years using the app and still find new and useful Excel tricks. Sometimes you’ll even find several ways of getting the same end result. Spend some time learning how to get the most out of Excel and you’ll be able to get a much better perspective on your data. Did you find this post helpful? Be sure to take a look around the rest of our blog for more interesting articles like this one.
