All of us want to work more efficiently. Unfortunately, you could spend hours painstakingly inputting data into a spreadsheet. Below are tips to speed up the common tasks you perform in Microsoft Excel.
Read the full article at TechRepublic.com
1: Use Table objects
Table objects treat data as a unit and consequently, take care of a lot of things automatically—reducing the amount of time you need to interact with the data. When you convert a data set to a Table, you get automatic filters and formatting.
Excel knows you're working in a Table, so pressing Tab cycles through the Table for effortless data entry. With a quick click, you can add a totaling row to the bottom of the Table. Headers are always visible, regardless of how many records you have. When you extend the Table by adding a column or a row, the Table extends formulas and formatting. Charts based on Table objects update automatically when you change the source data. You can accomplish all this manually without a Table, but a Table eliminates these tasks.
To convert a data set into a Table, click anywhere inside the data. Then, click the Insert tab and click Table in the Tables group. In the resulting dialog, specify whether your data has a header row, then click OK.
For a more detailed look at Excel's Table objects, read 10 reasons to use Excel's table object and How to vamp Excel Table objects for better readability and function.
2: Open several files at the same time
If you work with lots of workbooks open, you can save a bit of time by opening them simultaneously. Open Windows Explorer and use the Ctrl or Shift key to create a multi-file selection. When you've selected all the files you need, press Enter. That's it! The only limitation is that your workbooks need to be in the same folder. This isn't strictly an Excel tip; you can open all kinds of files this way.
3: Add multiple rows and columns at once
You probably know how to add a row or a column, but do you know you can use the same technique to enter multiple rows and columns? Simply select the same number of rows or columns you want to insert. For instance, to insert three columns between columns C and D, you'd select columns D, E, and F—three columns to the right of column C. Then, right-click the selection and choose Insert. (Or choose Insert Sheet Columns from the Insert dropdown in the Cells group on the Home tab.)
4: Quickly move or copy rows and columns
You can move a row or multiple rows by selecting the row(s) and then hovering over the top or bottom border until Excel displays the four-arrow cursor. At this point, you can drag and drop the selection—it couldn't be easier. To move column(s), use the left or right borders. If you want to copy the selection instead of moving it, hold down the Ctrl key while you drag and drop.
Related: Webinar Intro to Data Analytics
5: Generate a unique set of values
It's common to see values repeated in the same column of a data set. If you need a unique set of those values, you can use Excel's Advanced Filter feature as follows:
- Make sure the column has a header cell—it's required. Add header text if necessary.
- Click any cell in the column.
- Click the Data tab.
- Click Advanced in the Sort & Filter group.
- In the resulting dialog, click the Copy To Another Location option.
- Make sure the List range is correct; it doesn't always represent your clicked column.
- Click inside the Copy To control and enter the first cell of the copy range or click a cell in the sheet.
- Check the Unique Records Only option (Figure A).
- Click OK to create a unique list of values in the Copy To range (Figure B).
You can use this feature to copy a unique set of records by specifying the data set (instead of a single column) in the List range. This feature works equally well with a Table. However, I have found the feature a bit unpredictable when working with a normal range you've converted from a Table.