There are very, very few people on planet Earth who could ever say they’ve completely mastered every intricate little thing about Microsoft Excel. It’s the world’s premiere spreadsheet application, and has been the industry standard for almost 35 years, replacing the once-venerable Lotus 1-2-3, the first killer app for PCs in the 1980s.
Microsoft Excel’s dominance as a spreadsheet has yet to be truly tested, certainly not by Corel’s Quattro Pro (still sold today in WordPerfect Office Pro), the open source tools from Apache or LibreOffice, or even by Google’s Sheets (the spreadsheet part of Google Drive).
There’s a reason for that. Excel is more than a brand everyone knows: it is powerful. It does just about everything one could ask for in a spreadsheet. The current Excel 2019 version, available in Microsoft Office 2019 as part of a Microsoft 365 subscription and other methods, is a PCMag Editors’ Choice.
It’s not just for numbers. Plenty of people populate Excel’s seemingly infinite grids with data, using it as a flat-file database. It can make a relatively effective contact manager or full-blown customer relationship manager. It’s not all that shocking to see people using it as their word processor, despite Microsoft Word typically sitting right next to it.
One thing almost every Excel user has in common: not knowing enough. There are so many ways to slice and dice numbers, give that data a new look, and more, it’s impossible to recount them all. Entire books are written on the topic. But it’s easy to master some of the more interesting and intricate tips that will make your time using the program a little easier, and will make you look like your office’s guru of high-tech spreadsheetery. So bone up on any or all of these tricks to excel at Excel.
1. Paste Special to Transpose
You’ve got a bunch of rows. You want them to be columns. Or vice versa. You would go nuts moving things cell by cell. Copy that data, select Paste Special, check the Transpose box, and click OK to paste into a different orientation. Columns become rows, rows become columns.
2. Hide in Plain Sight
It’s easy to hide a row or column—just select the whole thing by clicking the letter or number header, right-click, and select “Hide.” (You can unhide by selecting the columns to either side all at once, right-clicking, and selecting “Unhide”). But what if you have just a little section of inconveniently placed data you want to hide, but you still want to be able to work with? Easy. Highlight the cells, right-click, and choose Format Cells. On the Number tab at the top, go to Category and select “Custom.” Type three semicolons (;;;) in the Type: field. Click OK. Now the numbers aren’t visible, but you can still use them in formulas.
3. Use Personal Workbook for Macros
When you do unhide an entire workbook, you’ll probably see a workbook listed you didn’t know you hid: the Personal.XLSB file. This is actually the personal workbook Excel created for you; it’s opened as a hidden workbook every time Excel starts. The reason to use it? Macros. When you create a macro, it doesn’t work across every single spreadsheet you create by default (like it does in Word)—a macro is tied to the workbook it was created in. However, if you store the macro in Personal.XLSB, it’ll be available all the time, in all your spreadsheet files. The trick is, when you record the macro, in the “Store macro in” field, select “Personal Macro Workbook.” (You can record a macro by turning on the Developers tab—go to File tab, select Options, click Customize Ribbon, then in the Main Tabs box, check Developers, click OK.)
4. Ctrl+Shift to Select
There are much faster ways to select a dataset than using the mouse and dragging the cursor. Click in the first cell you want to select and hold down Ctrl+Shift, then hit either the down arrow to get all the data in the column below, up arrow to get all the data above, or left or right arrow to get everything in the row (to the left or right, of course). Combine the directions, and you can get a whole column as well as everything in the rows on the left or right. It’ll only select cells with data (even invisible data). If you use Ctrl+Shift+End, the cursor will jump to the lowest right-hand cell with data, selecting everything in between. So if the cursor is in the upper-left cell (A1), that’s everything. Even faster: Ctrl+Shift+* (the asterisk) will select the whole data set no matter what cell is selected. (Image by xuuxuu from Pixabay)
5. Screenshot Insertion
Excel makes it ultra-easy to take a screenshot of any other open program on your desktop and insert it into a worksheet. Just go to Insert tab, select Screenshot, and you’ll get a drop-down menu displaying a thumbnail of all the open programs. Pick one to insert the full-sized image. Resize it as you desire.
6. Perform Quick Analysis
If you don’t know exactly what info you’d like to apply to data in Excel, try the Quick Analysis menu to run through options quickly. Select the data and click on the Quick Analysis box that appear on the lower right. You’ll get a menu that pops up with options to swiftly apply conditional formatting, create charts, handle totals, show sparklines, and more.
7. Multiple Cells, Same Data
For some reason, you may have to write the same thing over and over again in cells in a worksheet. That’s excruciating. Just click the entire set of cells, either by dragging your cursor, or by holding the Ctrl key as you click each one. Type it on the last cell, then hit Ctrl+Enter—and what you typed goes into each cell selected.
8. Great Excel Shortcut Keys
Excel, like any great software, has many excellent keyboard shortcuts. Here are some of the best.
Ctrl+; —Inserts today’s date.
Ctrl+Shift+:—Inserts the current time (the colon is what is in a clock reading, like 12:00).
Ctrl+Shift+#—Changes the format of a date.
Ctrl+5—Applies a strikethrough to the text in a cell.
Ctrl+0— Hides the current column.
Ctrl+9—Hides the current row.
Ctrl+F6—Switches between open workbooks (that is, open Excel files in different windows).
Ctrl+`—That’s the accent mark, up by the 1 key. This combo toggles the view in the sheet to show all the formulas.
Ctrl+PageUp or PageDown—Quick shift between the sheets in the currently open workbook.
F2—Start editing the current selected cell (much faster than double-clicking).
Shift+F10—Opens the right-click menu for the cell you’re in.
9. Flash Fill, Fastest Fill Alive
Flash Fill will smartly fill a column based on the pattern of data it sees in the first column (it helps if the top row is a unique header row). For example, if the first column is all phone numbers that are formatted like “2125034111” and you want them to all look like “(212)-503-4111,” start typing. By the second cell, Excel should recognize the pattern and display what it thinks you want. Just hit enter to use them. This works with numbers, names, dates, etc. If the second cell doesn’t give you an accurate range, type some more—the pattern might be hard to recognize. Then go to the Data tab and click the Flash Fill button. Check out this Microsoft tutorial for more.
10. Work With Cells Across Sheets
This one, called 3D Sum, works when you have multiple sheets in a workbook that all have the same basic layout, say quarterly or yearly statements. For example, in cell B3, you always have the dollar amount for the same corresponding week over time.
On a new worksheet in the workbook, go to a cell and type a formula like =sum(‘Y1:Y10′!B3). That indicates a SUM formula (adding things up) for all the sheets that are titled Y1 to Y10 (so 10 years’ worth), and looking at cell B3 in each. The result will be the sum of all 10 years. It’s a good way to make a master spreadsheet that refers back to ever-changing data.
11. Paste Special with Formulas
Let’s say you’ve got a huge amount of numbers in decimal format you want to show as percentages. The problem is, that numeral 1 shouldn’t be 100%, but that’s what Excel gives you if you just click the Percent Style button (or hit Ctrl-Shift-%). You want that 1 to be 1%. So you have to divide it by 100. That’s where Paste Special comes in.
First, type 100 in a cell and copy it. Then, select all the numbers you want reformatted, select Paste Special, click the “Divide” radio button, and boom goes the dynamite: you’ve got numbers converted to percentages. This also works to instantly add, subtract, or multiply numbers, obviously.
12. Use Graphics in Charts
You can put a graphic in any element of an Excel chart. Any element. Each bar, piece of pie, etc., can support its own image. For example, above, there’s a South Dakota state flag on the pie chart (placed by selecting the slice, using the Series Options fly-out menu, and selecting “Picture or texture fill”), plus an embedded PCMag logo (placed with the Insert tab’s Pictures button). You can even go with “no fill” at all, which caused that missing slice.
Clip art can be cut and pasted to an element—dollar bills to show dollars spent, water drips for plumbing costs, that kind of thing. Mixing and matching too many graphical elements makes it impossible to read, but the options you have are well worth tinkering with. Let the resident graphic designer check them out before you use them.
13. Conditionally Format
Looking at a huge amount of data and wondering where the highlights are? Who has the highest (or lowest) score, what the top five are, etc.? Excel’s Conditional Formatting will do everything from put a border around the highlights to color coding the entire table. It’ll even build a graph into each cell so you can visualize the top and bottom of the range of numbers at a glance. (Above, the highest numbers are in speedy green, the lowest in halting red, with a spectrum in between.) Use the Highlighted Cells Rules sub-menu to create more rules to look for things, such as text that contains a certain string of words, recurring dates, duplicate values, etc. There’s even a greater than/less than option so you can compare number changes.
14. Save Charts as Templates
Excel has more types of charts than Jimmy Carter’s got teeth and peanuts, but it’s almost impossible to find a default chart that is perfect for your presentation. Thankfully, Excel’s ability to customize all those graphs is exemplary. But when you have to recreate one? That’s a pain. It doesn’t have to be. Save your original chart as a template.
Once a chart is perfected, right-click on it. Select Save as Template. Save a file with a CRTX extension in your default Microsoft Excel Templates folder. Once you do that, applying the template is cake. Select the data you want to chart, go to the Insert tab, click Recommended Charts, and then the All Charts tab, and the Templates folder. In the My Templates box, pick the one to apply, then click OK.
Some elements, like the actual text in the legends and titles, won’t translate unless they’re part of the data selected. You will get all the font and color selections, embedded graphics, even the series options (like a drop shadow or glow around a chart element).
15. Validate Data to Make Drop Downs
Creating a spreadsheet for others to use? If you want to create a drop-down menu of selections to use in particular cells (so they can’t screw it up!), that’s easy. Highlight the cell, go to the Data tab, and click Data Validation. Under “Allow:” select “List.” Then in the “Source:” field, type a list, with commas between the options. Or, click the button next to the Source field and go back into the same sheet to select a data series—this is the best way to handle large lists. You can hide that data later, it’ll still work. Data Validation is also a good way to restrict data entered—for example, give a date range, and people can’t enter any dates before or after what you specify. You can even create the error message they’ll see.
16. Text to Columns
Say you’ve got a column full of names, first next to last, but you want two columns that break them out. Select the data, then on the Data tab (at the top) click Text to Columns. Choose to separate them by either delimiters (based on spaces or commas—great for CSV data values) or by a fixed width. Fixed width is utilized when all the data is crammed into the first column, but separated by a fixed number of spaces or period. The rest is like magic, with extra options for certain numbers.
17. Insert Excel Data Into Word
Thirty-five years ago, the thought of placing data from Excel into Word or PowerPoint was mind-blowing in the world of Office Suites. Today, there’s nothing to it. Whether you’re taking data cells or a full-blown graphical chart, copy and paste into the other program. The thing to be aware of is, this is a link-and-embed process—if you change the data in the spreadsheet, it’ll change in the Word DOC or PowerPoint PPT, too. If you don’t want that, paste it as a graphic. Use Word’s own Paste Special tool for that. Or, when taking it from Excel, go to the Home tab at top, select the Copy menu, and use the Copy as Picture option. Then you can paste the graphic into any program at all.
18. Hide A Whole Sheet
Your typical Excel workbook—the file you’re working in—can get loaded with plenty of worksheets (each sheet indicated by a tab at the bottom, which you can name). Hide a sheet if you want, rather than delete it, making its data still available not only for reference, but also available to formulas on other sheets in the workbook. Right-click the bottom sheet tab and select Hide. When you need to find it again, you have to go the View tab at top, click Unhide, and pick the sheet name from the list that pops up.
There is also a Hide button on the View tab menu at top. What happens when you click that? It hides the entire workbook you’re using. It looks like you closed the file, but Excel keeps running. When you close the program, it’ll ask if you want to save changes to the hidden workbook. When you go to open the file, Excel gives you what appears to be a blank workbook—until you click Unhide again.
19. Pivot! Pivot!
Whole books have been devoted to PivotTabels. They’re summaries of your giant collection of data that makes it much easier to parse the info based on your reference points. For example, if you’ve got the entire set of grades for all your students across all tests for the whole year, a PivotTable can help you narrow things down to one student for one month. It behooves anyone with big data to play with them (make a copy of the original data to play with first).
To create one, check that all the columns and rows are titled the way they should be, and then select PivotTable on the Insert tab. Better yet, try the Recommended PivotTables option to see if Excel can pick the right kind for you. Or try the PivotChart, which creates a PivotTable with an included graph to make it easier to understand.
20. Line Breaks and Wrapping Text
Typing into spreadsheet cells can be frustrating, as the default for text you type is to continue on forever, without wrapping back down to a new line. You can change that. Create a new line by typing Alt+Enter (hitting Enter alone takes you out of the cell). Or, click the Wrap Text option under the Home tab at the top of the screen, which means all text wraps right at the edge of the cell you’re in. Resize the row/column and the text re-wraps to fit.
21. Use $ to Prevent Shift
When you write a formula, you reference cells by their position, such as A1. If you copy a formula and paste it in the next cell down, Excel will shift that referenced cell, so it would say A2 instead. To prevent shifting, use the dollar sign ($). Type $A1 and cut and paste it to a new cell, for example, which prevents a shift in the column (A); A$1 prevents the shift in the row (1), and $A$1 prevents the shift change in any direction when copying a formula.
This is handy when you have a single cell to use in a whole bunch of formulas. Say you want to divide everything by 100. You could do a formula like =(A1/100), but that means you can’t change the 100 easily across the board. Put the 100 in cell B1 and use =(A1/B1)—but then when you cut and paste it down, it turns to =(A2/B2), then =(A3/B3), etc. The $ fixes that: =(A1/$B$1) can be cut and pasted down a row, but the $B$1 reference never changes. Then you can change the value of 100 in the cell as needed to experiment with other changes.
22. AutoFill Your Cells
This is a no-brainer, but so easily overlooked. You start typing a series of repetitive things like dates (1/1/20, 1/2/20, 1/3/20, etc.) and you know you’re in for a long day. Instead, begin the series and move the cursor on the screen to the lower-right part of the last cell—the fill handle. When it turns into a plus sign (+), click and drag down to select all the cells you need to fill. They’ll magically fill using the pattern you started. And it can also go up a column, or left or right on a row.
Even better—you can Auto Fill without much of a pattern. Again, pick a cell or cells, move to the fill handle, click, and drag. You’ll get a menu of options. The more data you input at first, the better the Fill Series option will do creating your AutoFill options. Check out this Microsoft tutorial.
23. Microsoft Word Tips You Need to Learn Now
Think you know everything about Microsoft Word? Here are some hidden tricks and time-saving hacks you need to know about Redmond’s word-processing app.
24. Paint Cells to a New Format
Let’s say you change not only the wrapping in a cell, but also the entire look—the font, the color, whatever. And you want to apply it to many, many other cells. The trick is the tool, the one that is on the Home tab that looks like a paint brush.
Select the sell you like, , and then click on a different cell to paint in the format—they’ll match in looks, not in content. Want to apply it to multiple tabs? the paint brush icon, then click away on multiple cells.
25. Quickly Add Without Formulas
Got numbers in a spreadsheet you want a quick calculation on, without the hassle of going to a new cell and creating a SUM formula for the job? Excel now offers a quick way to do that. Click the first cell, , and click a second cell. Look at the at the bottom and you’ll see the sum of the cells calculated for you.
Keep your finger on Ctrl and click as many cells as you like, the status bar will continue to show the sum for all the cells. (Click a cell with letters/words as the content, it gets ignored.) Better yet, right click the status bar to get the Customize Status Bar menu and you can choose to add other elements that can be quick-calculated like this, such as seeing the average or count of how many cells you clicked (or the numerical count, which is how many cells you clicked that actually have numbers).
26. Freeze Headers for Scrolling
Working with a massive data set in a spreadsheet can be difficult, especially as you scroll up/down or left/right and the rows and columns may be hard to track. There’s a simple trick for that if you’ve got a header row or column, where the first row/column has a descriptor. You freeze it so when you scroll, that row and/or column (or multiple rows and/or columns) don’t move.
Go to the and look for . You can easily just freeze the top row (select ) or first column (select ). You can do both at once by clicking the cell at B2 and just selecting . This is where it’s fun—select any other cell and also Freeze all the panes above and left of it. Select cell C3 for example and the two rows above and two columns to the left won’t scroll. You can see it in the screenshot above, indicated by the darkened grid lines.
When you want to get rid of the freeze, you can just select from the menu.
27. New Window for Second View
Spreadsheets can be enormous, and you may have to interact with different areas of the spreadsheet at what seems like the same time, such as cutting and pasting info from the top to the bottom over and over. If it’s hundreds of thousands of cells, the scrolling that would take could make you nauseous. Or, you could just open a second window on your desktop with a view of exactly the same spreadsheet. It’s easy. In the , click . You can also click to get them ordered on screen in a way that works for you. You can see them Horizontally arranged above. Then, type something into a cell in one window, you can see it appear in the other window. This trick is especially handy if you’ve got dual monitors.