Last week, we shared a few tips for Microsoft Word. This week, we turn our attention to Microsoft Excel. Excel remains the go-to spreadsheet application even after 35 years. While there are other spreadsheet applications out there, Excel does everything they do and more. With that in mind, it’s nearly impossible to be an Excel expert. However, you may feel like one after learning these tips!
1. Add Sums Quickly
The SUM function is used probably more than any function in Excel. That’s probably why they made a keyboard shortcut for it. Select a cell immediately adjacent to a row or column of numbers. Press Alt+= on Windows (Command+T on Mac). Excel will automatically detect the nearest range of numbers, use it within the SUM function, and display results near instantaneously.
2. View Formulas
Not sure where your values end and your formulas begin? A quick press of Ctrl+` (that’s the accent symbol above the Tab key) will show you the raw data for every cell. It’s much faster than clicking a cell to see the contents in the Formula Bar.
3. Transpose With Paste Special
Have you ever wished you’d organized your data by columns instead of rows or vice versa? The Transpose feature easily moves your data from rows to columns and vice versa. If you use Excel long enough, you’ll realize how beneficial this can be. It’s infinitely better than retyping an entire range of data.
To start, select any range you want to transpose and copy it. Now, select the top-left cell where you’d like the transposed version to begin. Then, either click Paste Special on the Home tab or press Ctrl+Alt+V (Command+Ctrl+V on Mac). Check the Transpose box at the bottom right and click OK. Excel rearranges your data, formulas included, in an instant.
4. Ctrl+Shift Is Your Friend
Clicking and dragging can be tedious when selecting large ranges of data. Thankfully, there’s a much faster way. Click the first cell that you want to select. Now, hold Ctrl+Shift (Command+Shift on Mac) and press an arrow key in the direction you want to select. The right and left arrows will select through the end or beginning of the data in that row. The up and down arrows will select all data above or below. This is also a great way to navigate your data without selecting anything. Just hold Ctrl and use the arrows without the Shift key.
5. Wrap Text & Line Breaks
One of the most frustrating things about Excel is never-ending lines of text. Cells hide text that extends beyond its boundaries (unless the following cell is empty). You can manually add your own line breaks by typing Alt+Enter.
For an even better method, you can click the Wrap Text button on the toolbar. This feature makes the cell taller and wraps the text so that it stays within the width of the column. If you resize the column, the text adjusts to fit!
6. Auto Fill
Hopefully, you’ve already discovered Excel’s ability to Auto Fill. If not, feel free to buy us lunch. In Excel, you’ll often find yourself entering repeating patterns of data like a series of dates, times, months, etc. Typing each one can take a while.
Instead, just enter the first 3 or 4, then select all of them. Move your cursor to the bottom right corner of the selection. You should see your cursor change from the standard white plus sign to a solid black plus sign (see image). You’re hovering over the fill handle.
While hovering there, click and drag to select the areas you want the data to fill. Once selected, release the mouse button, and Excel will automatically continue the pattern you started. You’re not even limited to just a single row or column.
7. Add The Same Data To Multiple Cells
Occasionally, you need the same text or value in multiple places all over your spreadsheet. Sure, you can copy and paste data wherever you want. However, this feature can be much faster. You can select a range of cells or randomly select cells by holding Ctrl key (Command on Mac) as you click each one. After selecting the last cell, type whatever data you need, but don’t hit Enter or Tab. Instead, hit Ctrl+Enter (Command+Enter on Mac), and Excel will add that text to every selected cell.
8. Split Text Into Columns
Suppose you have a list of full names, but you want to split first and last names into separate columns. Once again, Excel makes this task a breeze. Select your list. Now, click on the Data tab, then click the Text to Columns button. You’ll likely use Delimited most of the time. Check the box for the delimiter separating your text (that would be a space in our example). Click the Finish button, and watch your list of names break apart into two separate columns.
Looking for More Tips?
Blog posts with various IT tips and news are released every Friday. We publish new episodes of Tech Tip Tuesday as often as we can. You can view previous episodes in our Tech Tip Tuesday library. Click the Sign Up or Subscribe button on this page to subscribe and receive every tip directly in your inbox each week. Pit Crew IT Services can also help your organization with any IT needs you might have. Get started with a free consultation using the button below.