10 Ways to Become a Spreadsheet Pro by Making Microsoft Excel Work Smarter and Faster

For years, Microsoft Excel has represented the gold standard when it comes to spreadsheet software. Although firm market share figures specific to Excel use are hard to come by, Microsoft Office, the productivity suite in which Excel comes packaged, still claims approximately 80% to 96% of worldwide user share. As PCMag wrote in 2013, Excel is “far and away the most powerful, and now the easiest to use worksheet app… the first and only choice for manipulating numeric data.”
Believe it or not, there are countless ways to supercharge your Microsoft Excel experience: some built directly into the program’s interface, and some available as free external plug-ins. Nobody ever said Excel was easy, and nobody can ever become a master of the complex program. But following the 10 methods below can help you wring more productivity and efficiency out of Excel.

1. Use Ctrl+Shift to select a data set. Using the mouse and dragging the cursor can be slow compared to this quick and easy keyboard shortcut. Click the first cell you want to select, hold down Ctrl+Shift, and use the arrow keys to collect all the data you want to copy or cut. Press Ctrl+Shift+End to jump to the lowest right-hand cell and select everything in between, or use Ctrl+Shift+* and select the whole data set no matter which cell is selected. Alternatively, clicking the corner button of any worksheet will select the entire data set in seconds.

2. Apply similar formatting to multiple sheets at the same time. This critical task is a breeze with Excel’s grouped worksheets feature. Click the tabs of the sheets you want to group together while pressing CTRL and they’ll all turn white; while the sheets are grouped, any formatting entered on one sheet also shows up in the others. After formatting grouped sheets, make sure to click on tabs to ungroup them before entering or removing data.

3. Remove duplicates from a data set. If you’re working with a large body of data, you’ve probably got duplicate content lurking around somewhere. Luckily, removing it is easy: highlight the row or column you want to assess for duplicate entries, click the Data tab, then Tools, then select Remove Duplicates. A pop-up will prompt you to confirm which information you want to work with and remove to prevent data loss.

4. Insert rows and columns with ease. A good Excel spreadsheet is always ready to grow. Quickly adding rows and columns can be done in a variety of ways: clicking Insert > Rows or Columns; right-clicking on rows or columns and choosing Insert; or pressing the CTRL and + or CTRL, SHIFT, and + keys.

5. Rely on simple calculations to save time and energy. Excel’s formulas can be legendary for their complexity, but simple calculations can often give you the most immediate boost in productivity. To add, use the + key; to subtract, use the – key; to multiply, use the * key; and to divide, use the / key.

6. Insert Excel data into Microsoft Word or PowerPoint. If you’ve ever tried this before, you might laugh. But transferring data from a spreadsheet to a text document is possible. Just be careful to click the clipboard that shows up after you paste to decided whether you’d like to keep the original Excel formatting or have the pasted data match the format of your existing Word or PowerPoint document.

7. Dig deep into data with PivotTable reports. This interactive Excel option allows users to see the big picture contained within a data set by automatically extracting, organizing, and summarizing data. Select either a whole table or individual cell and click on Insert > PivotTable —then visit Microsoft’s PivotTable Support page for further tutorials.

8. Use AutoFill. This works great for repetitive tasks: filling in dates, times, or other values that follow a sequence. Start with the first entry, then move the cursor over the lower right section of the final cell, which brings up a fill handle. Once it changes to a plus (+) sign, click and select all the cells you need to fill with your chosen pattern. You can even go up a column or left and right on a row.

9. Open multiple worksheets with one double-click. Working on multiple worksheets one day and know you’ll do it again the next? Before you shut down Excel, click on View > Save Workspace, then enter a name and location for your saved view. An icon will appear in the location of your choice — clicking on that icon takes you back to the same worksheet set.

10. Hide data in protected worksheets. If you want to share some data with other users but not all of it, consider hiding it. The easy way is to right click and select the Hide function. But you can be even safer by using the Format Cells function by clicking Home>Font>Open Format Cells>Number Tab>Custom>Type. That way, your data can stay truly safe.

Email from Lawrence Sschweitzer