Few people on Earth can say they’ve completely mastered everything about Microsoft Excel. As the world’s premier spreadsheet software, it has been the industry standard for decades, replacing the once-venerable Lotus 1-2-3, the first killer app for PCs in the 1980s.
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), the open-source LibreOffice, or Apple’s Numbers. Google might have an edge on the user numbers but its online-and-app-only Sheets has nowhere near the power of Excel running on a desktop. (Note that you can also use a limited version of Excel and other Office apps for free online.)
Desktop-based Excel simply does everything one could ask for in a spreadsheet. The current version, available as part of a Microsoft 365 subscription and other bundles, remains our PCMag Editors’ Choice.
Don’t think that Excel is just for numbers. Many 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. Not to mention the array of excellent-looking charts it can generate with the right (or even wrong!) data.
One thing almost every Excel user has in common is not knowing enough about the software. There are so many ways to slice and dice data that discussing them all is impossible. Entire books are written on the topic. But it’s easy to master some techniques that will make your life easier—and make you look like a guru. Bone up on any or all of these tricks to excel at Excel.
1. Select Everything, Everywhere All At Once
Yes, you can select all the data in the worksheet you’re looking at with a Ctrl-A. But you can also just click the little box at the upper left corner, above the 1, left of the A column, to do the same.
2. Paint Cells to a New Format
(Credit: PCMag/Microsoft)
Let’s say you change not only the wrapping in a cell, but also the entire look—the font, the color, whatever. Now you want to apply that look to many, many other cells. The trick is the Format Painter tool; it’s on the Home tab and looks like a paintbrush.
Select the cell you like, click the paintbrush icon, 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? Double-click the paintbrush icon, then click away on multiple cells.
3. Add Line Breaks and Wrapping Text
Typing into spreadsheet cells can be frustrating, as the default is text that continues 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, which will make all text wrap right at the edge of the cell you’re in. Resize the row/column, and the text re-wraps to fit.
If you’ve got multiple cells with text overruns, select them all before you click Wrap Text. Or select all the cells before you even type in them and click Wrap Text; then whatever you type in the future will wrap.
4. Autofit All the Columns/Rows Instantly
(Credit: PCMag/Microsoft)
If you’ve got a sheet full of truncated cells (with no text wrap) that no one can read, manually clicking column and row headers one by one helps with readability—but it could also take a while. Instead, do Ctrl+A to select all, then tap Alt+HOI (hit the letters in that order). That will autofit all the columns. Next, use Alt+HOA to autofit the rows. Every cell will be instantly readable, though you may need to use the scrollbar to see everything on the sheet. You can also find those commands using Home > Format > Autofit Row Height or AutoFit Column Width.
5. AutoFill Your Cells
This is a no brainer, but so easily overlooked. Say you start typing a series of repetitive things like dates (1/1/25, 1/2/25, 1/3/25, etc.). You know you’re in for a long day. So instead, begin the series and move the cursor on the screen to the lower-right part of the last cell to get the AutoFill handle—it looks like a plus sign (+). Click and drag to select all the cells you want to fill. They’ll magically fill using the pattern you started—up or down a column, or left or right on a row.
Even better: Try Auto Fill without much of a pattern. Again, pick a cell or cells, move to the fill handle, right-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 with your AutoFill options. Check out this Microsoft tutorial for more.
6. Flash Fill: Fastest Fill Alive
(Credit: PCMag/Microsoft)
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 formatted like “21255554111” and you want them to all look like “(212)-555-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.
7. Ctrl+Shift to Select
There are faster ways to select a dataset than using the mouse and dragging the cursor, especially in a spreadsheet containing hundreds of thousands of rows or columns. 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, plus 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, even blank cells. So if the cursor is in the upper-left cell (A1), that’s everything.
Ctrl+Shift+* might be faster, as it will select the whole contiguous data set of a worksheet, but it will stop at blank cells.
8. Drag That Data Around
Got a column you wish were a few columns to the left? Highlight the column and move the cursor to the edge, and when it turns into a series of arrows pointing all directions, click and drag it where you want. If it’s over existing data, Excel will ask you first if you truly want to proceed. Press the Ctrl key before you drag it to copy the data to the new spot, so you get a duplicate.
If you hold down the Alt key, you can drag the data right off the worksheet—move the mouse cursor to a tab at the bottom, and when it opens, you can drop the data there.
9. Text to Columns
(Credit: PCMag/Microsoft)
Say you’ve got a single 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 used 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.
10. 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 it into a different orientation. Columns become rows; rows become columns.
11. Multiple Cells, Same Data
For some reason, you may have to write the same thing repeatedly in cells in a worksheet. That’s excruciating. Just click the entire set of cells, by dragging your cursor or holding the Ctrl key as you click each. Type what you want on the last cell, then hit Ctrl+Enter (not Enter alone)—what you typed goes into each selected cell. This also works with formulas.
12. Paste Special With Formulas
(Credit: PCMag/Microsoft)
Let’s say you’ve got a huge set of numbers in decimal format that 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 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: You’ve got numbers converted to percentages. This also works to instantly add, subtract, or multiply numbers.
13. Save Charts as Templates
Excel’s ability to customize all graphs is exemplary. But when you have to recreate a similar chart entirely from scratch, that’s a pain. Instead, 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 that’s done, simply reapply that template to new charts. Select the data you want to chart, go to the Insert tab, click Recommended Charts > All Charts tab > 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).
14. Use Graphics in Charts
(Credit: PCMag/Microsoft)
You can put a graphic in any element of an Excel chart. We mean any element. Each bar, piece of pie, etc., can support its own image. For example, put a state flag on a pie chart (select the slice, using the Series Options fly-out menu, and choose Picture or texture fill), or embed a logo (place it with the Insert tab’s Pictures button). You can even go with “no fill,” which makes a slice of a pie chart “disappear.”
Clip art can be cut and pasted to an element—dollar bills to show dollars spent, dripping water for plumbing costs, that kind of thing. Mixing and matching too many graphical elements makes a chart impossible to read, so have your resident graphic designer check everything out before you go ahead.
15. Work With Cells Across Worksheets
3D Reference works when you have multiple sheets in a workbook with the same basic layout. 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. It will work with other functions, like averages, counts, finding minimums and maximums, etc.
16. 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 simultaneously, right-clicking, and selecting Unhide). But what if you have just a little section of inconveniently placed data you want to hide and you still want to be able to work with the data? 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.
17. Hide a Whole Sheet
(Credit: PCMag/Microsoft)
Your typical Excel workbook—the file you’re working in—can get loaded with plenty of worksheets (each 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 for formulas on other sheets in the workbook. Right-click the tab and select Hide. When you need to find it again, go to the View tab up top, click Unhide, and pick the sheet name from the list.
There is also a Hide button on the View tab menu up top. When you click it, 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.
18. Use the Personal Workbook for Macros
When you unhide an entire workbook, you’ll probably see a workbook listed you didn’t know you hid: the Personal.XLSB file. This is 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 spreadsheet you create by default (like in Microsoft Word)—a macro is tied to the workbook in which it was created. However, if you store the macro in Personal.XLSB, it will be available all the time, in all your spreadsheet files.
The trick is, when you record the macro, in the Store macro in field, you should select Personal Macro Workbook.
You 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.
19. Pivot! Pivot!
(Credit: PCMag/Microsoft)
Whole books have been devoted to PivotTables. They’re summaries of your giant collection of data, which make 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 narrow things down to one student for one month. It behooves anyone with big datasets to play with them (make a copy of the original data first).
To create a PivotTable, 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. Drill Down on Pivot Table Sources
Want to know where some numbers came from on your pivot table? Just double-click on the cell in the pivot. An entirely new tab (worksheet) will be generated in your workbook. The contents of that tab only showsthe data that was used to calculate that single cell. It’s very handy when you can’t understand why certain things appear in the pivot.
21. Slice Into Some Data
(Credit: PCMag/Microsoft)
Whether you’re using a big, formatted table of data in a worksheet or a full-on pivot table that’s equally stuffed, you can filter the data down pretty quickly and easily with a Slicer. Whenever your cursor is in the table/pivot table, select Slicer from the Insert tab, or on the PivotTable Analyze menu tap Insert Slicer, or right-click on an entry in the pivot table fields and select Slicer.
Get Our Best Stories!
Your Daily Dose of Our Top Tech News
By clicking Sign Me Up, you confirm you are 16+ and agree to our Terms of Use and Privacy Policy.
Thanks for signing up!
Your subscription has been confirmed. Keep an eye on your inbox!
A floating menu will appear; pick a data point. The data becomes a permanent floating menu that you can use to quickly narrow down results. Modify each floating slicer via the Slicer tab that will now appear. You can hold the control button down to select more than one item, and easily clear selections by clicking the funnel with a slash icon at the upper right. Right-click the slicer to go to settings and turn off Display Header if you don’t need the header, or change it to show something else.
22. 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. Select the data and click on the Quick Analysis box that appears in the lower right. You’ll get a menu of options to swiftly apply conditional formatting, create charts, handle totals, show sparklines, and more.
23. Quickly Customize Your Data Table
(Credit: PCMag/Microsoft)
A table in Excel involves taking raw rows/columns of data and slapping a format on it that makes it easily sortable. As noted above, you can quickly turn whatever data you select into a table with Quick Analysis. But if you take the time to go to the Home tab to select Format as Table, you’ll see a vast array of other table designs that might work better for you (some with header rows and columns, some without). This is also where you go to change a table’s format.
If you’ve got a style you’d like to try, go directly to New Table Style in the menu and create your own. In the future, it will appear at the top of the menu under the Custom banner.
(Don’t care about the format? Just hit Ctrl+T to quickly apply a table.)
24. 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 those others 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 will still work.
Data Validation is a good way to restrict data entry. For example, you can give a date range, and people won’t be able to enter any dates before or after what you specify. You can even create the error message they’ll see.
25. Conditionally Format Tables
(Credit: PCMag/Microsoft)
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 show you, from creating a border around the highlights to color coding the entire table. It’ll even build a graph into each cell to visualize the top and bottom of the range of numbers at a glance. 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 to compare number changes.
26. 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 dropdown menu displaying a thumbnail of all the open programs. Pick one to insert the full-size image. Resize it as desired.
27. Pull Data from Pictures
(Credit: PCMag/Microsoft)
Yes, you can insert images into Excel easily. But what if that picture is full of data you want to place in a spreadsheet—as in, you took a picture of a spreadsheet? You could retype it all, but that’s going to take forever. And unnecessary. Go to Data > From Picture > Picture From File to import, or do it from the clipboard if you want to take a screenshot (Data > From Picture > Picture From Clipboard), which is handy if you see a sample sheet on a website. You’ll see a pane called Data from Picture display the import analysis happening in real time, and then the data will appear in your worksheet.
28. Insert Excel Data Into Word
Thirty-five years ago, the thought of placing data from Excel into Word or PowerPoint was mindblowing. Today, there’s nothing to it. Whether you’re taking data cells or a full-blown graphical chart, copy and paste it into the other program. Beware: 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.
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.
29. Use a Dollar Sign ($) to Prevent Shift
(Credit: PCMag/Microsoft)
When you write a formula, you reference cells by their position, such as A1. If you copy a formula and paste it into the next cell down, Excel shifts that referenced cell, so it will say A2 instead. To prevent shifting, use the dollar sign ($). Type $A1 and cut and paste it to a new cell, for example, which will prevent 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 many 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.
30. Great Excel Shortcut Keys
Excel, like any great software, has many excellent keyboard shortcuts. Here are some of the best:
Recommended by Our Editors
-
Ctrl+; —Inserts today’s date in a cell.
-
Ctrl+Shift+: —Inserts the current time.
-
Ctrl+Shift+# —Changes the format of a date.
-
Ctrl+5 —Applies a strike-through (
like this) 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 Ctrl+PageDown —Quick shifts between the sheets in the currently open workbook.
-
F2 —Start editing the currently selected cell (much faster than double-clicking).
-
Shift+F10 —Opens the right-click menu for the cell you’re in.
-
Shift+F2 —Allows you to add a comment to the cell.
-
Ctrl+Shift+V —This pastes whatever you copied as a value—so if you copy a number that is generated by a formula, it pastes just the final value, not the formula itself. (Ctrl+V would paste the formula.)
-
Ctrl+T —Click first on any data you have; this shortcut instantly transforms it into a table.
31. Quickly Add Without Formulas
(Credit: PCMag/Microsoft)
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 offers a quicker way. Click the first cell, hold down the Ctrl key, and click a second cell. Look at the status bar 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, and it gets ignored.)
Better yet, right-click the status bar to access the Customize Status Bar menu and add other elements that can be quickly calculated, 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).
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. Freeze it, so when you scroll that row and/or column (or multiple rows and/or columns) doesn’t move off screen.
Go to the View tab and look for Freeze Panes. You can easily just freeze the top row (select Freeze Top Row) or first column (select Freeze First Column). You can do both at once by clicking the cell at B2 and selecting Freeze Panes. This is where it gets fun—select any other cell and also Freeze all the panes above and left. Select cell C3 for example, and the two rows above and two columns to the left won’t scroll.
When you want to eliminate the frozen cells, select Unfreeze Panes from the menu.
33. New Window for Second View
(Credit: PCMag/Microsoft)
Spreadsheets can be enormous. Yet you may have to interact with different areas of the spreadsheet simultaneously, 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 can 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 View tab, click New Window. You can also click Arrange All to get them ordered on screen in a way that works for you. Type something into a cell in one window, and you can see it appear in the other window. This trick is especially handy if you’re using dual monitors.
34. Customize the Quick Access Bar
You may see the Quick Access Bar above or below the ribbon. It usually has just a couple of things in it to click, for saving, undo, and repeat. But you can fill that toolbar with useful, clickable shortcuts. Go to File > Options > Quick Access Toolbar. Pick from popular commands on the left and click Add>> to put them on the right—they’ll now reside in the toolbar. You aren’t limited to popular commands, however—click that dropdown on the top left, and you’ll see access to commands from every tab. Use All Commands to pick from the entire catalog of Excel options. And if you’ve got macros made, use this option to put the macro command on a toolbar (for that day when you can’t remember the keystroke you assigned to the macro).
35. Combine Text from Different Cells
(Credit: PCMag/Microsoft)
The ampersand symbol (&) can be used in a formula to put together text found in other cells. Check out the shot above—it shows names in columns A, B, and C; the “formula” of =A1&B1&C1 in column D mushes the names all together. This is called concatenation. If you want a space between the items you are concantenating, use quotes around a space, like this: =A1&” “&B1&” “&C1 (as shown in column E). Optionally, an Excel function called CONCAT will do it, too.
36. Combine Multiple Workbooks Into One
If you have a bunch of workbooks all formatted exactly the same, you could copy and paste them all into one. But that can be tough when Excel sheets have hundreds of thousands of rows. Instead, put all those files in the same folder. Go to the Data tab on the ribbon and select Get Data > From File > From Folder, pick your folder and click Open. You’ll get a window showing the name of each file; at the bottom, click the Combine menu and select Combine & Load to… Another window will pop up called Combine Files, with a sample of what you’ll get; click OK.
You’ll now get an Import Data dialog box asking if you want this to be in a table, a PivotTable Report, or a regular PivotTable. You can also choose to put it in a new worksheet or an existing worksheet. This can take time if it’s a big set of files. Once done, all your files will be one, with an added column showing the name of the original data file, in case you need to narrow things down further.
37. Link Between Tabs (Worksheets) or Cells
One workbook can contain many worksheets (each one gets a tab). Sometimes it helps to have links between them all, as navigating the tabs at the bottom can be a pain once you have too many. To link from one sheet/tab to another, select a cell, right-click and select Link (it’s also on the Insert tab), and in the pop-up, select “Place in this document.” You pick the cell you want to link to on that sheet (to jump up or down) or pick a different sheet you want to link to (again, specify a cell, if you desire). If you don’t pick text for the link in the cell, Excel will use the name of the tab.
To change it later, right click the cell to select Edit Hyperlink. You can also Remove Hyperlink. Once you’ve made a bunch of linked cells, you can copy and paste them into other sheets in the workbook, so the links appear wherever you work.
(Credit: PCMag/Microsoft)
38. Place Same Data on Every Sheet
Open an Excel workbook (file) and select several tabs on the bottom; do this by holding down the CTRL key. Once done, the word Group should appear in the Excel title bar, after the name of the workgroup. Go to a cell in one of the sheets—preferably a cell with no data in any of the sheets you picked—and type in something. Once done, what you typed will appear in that same cell across all the tabs.
You can go crazy with this and actually paste in data that crosses multiple cells. To do this, after you copy it, select a bunch of tabs using CTRL, then pick Fill (on the File tab at top) > Across Worksheets. (This works incredibly well when you link to tabs—as in the tip above—so you can create a linkable menu that appears on each sheet.)
39. Use AI via Copilot in Excel
Of course, generative artificial intelligence is now part of Excel, if you’re willing to pay extra for it. To use it, you’ll need not only a Microsoft 365 subscription but also a $20/month subscription to Copilot Pro. Even then, you can only use Copilot Pro while in Excel if your files are saved either to OneDrive or SharePoint with AutoSave turned on. After all that, you’ll see Copilot on the ribbon above and can get it started analyzing data for you. Get all the details in our story on how to use Copilot Pro AI with Microsoft 365 Apps.
40. Find Formulas with Any AI
(Credit: PCMag/Microsoft)
Are you stuck with only Copilot Pro for getting AI help with Excel? Definitely not.
Any generative AI—ChatGPT; Google’s Gemini; even the free versions of Copilot built into Microsoft Windows, Edge, and Bing—will feed you formulas if you ask it right. They’ll also usually spit out an explanation about what the formula is doing, so you can learn something along the way. There are plenty of third-party AI generators that specifically target this kind of approach, with names like Excel Formulator, ExcelFormulatBot, and GPTExcel, many of which will charge you a subscription fee.
Essentially, if you want an AI to look at your data and offer analysis, you have to pay. But if all you need is a little bit of help coming up with the right formula, you can typically do it for free with most major AI chatbots.
Make sure to specify that you need the AI-created formula to work in Excel, or if you need it to work in both Excel and Google Sheets, if that’s important. Be explicit and clear—reference your existing data so the AI knows, for example, that people’s gross pay rate is in column F, while their net pay is column G—that kind of thing. Then copy and paste the formula generated into the appropriate cell in your spreadsheet. Just be sure to check it over for accuracy before you replicate it in a full column or row.
41. Format Multiple Tabs
Let’s say you want to apply the same formatting to multiple tabs of data—for example, you want the first row in every tab to have bold text. Click a tab, hold down Shift, and then select the last tab. This selects them all. Or use Cntrl to pick select tabs. Do your formatting on the first tab. Then click through and you’ll see it on every tab you selected. If you change the look of cell A2, for example, all the A2 cells on the other tabs will get the same formatting.
42. Hide the Zeros
You may have a spreadsheet full of numbers, but some of those cells may amount to nothing, a goose egg, a total zero. If you want to hide zeros, you can, across the whole program. Go to File > Options > Advanced and scroll all the way down to Display options for this worksheet. Uncheck the box next to Show a zero in cells that have zero value, and they’ll appear as a blank. This is also where you can do things like hide gridlines, outline symbols, and even row and column headers, if you don’t like to see those kinds of things.
43. Autosum Columns and Rows
There is a keyboard shortcut that automatically adds together…everything. Pick a cell at the bottom of a column of numbers, hit Alt+= (the alt key plus the equal sign), and Excel will grab all the cells above and put them into a =SUM() formula. Nice and quick. It works on multiple columns; select all the cells in a row below a range of numbers, hit Alt+=, and all the columns get added up. Select cells to the right and all the rows get added. Select the whole range of original numbers and extend the selection down and to the right, hit Alt+=, and both columns and rows get added up.
To do it with multiple ranges of numbers where you’ve already used Alt+=: Select them all, extend the selection again, hit Alt+= again, and only the auto-sum column or rows get added up, because Excel is just that smart.
44. Master Xlookup
In 2019, Microsoft introduced the Xlookup function as a major upgrade to the Vlookup function that most Excel power users are familiar with (it also essentially replaces the Index Match function). Mastering Xlookup can be the real difference in taking your Excel-ing to the next level. (Note: HR departments want people with this skill.) The improvement comes from Xlookup’s ability to search horizontally and vertically, whereas the old Vlookup could only go vertical (there was a separate Hlookup for horizontal search).
What all these things do is look up something in a column or row, then return matching info from another column or row. This video from Microsoft gives you a quick overview of how it works, but there are hundreds more you can find on YouTube.
About Eric Griffith
Senior Editor, Features
