One thing almost every Excel user has in common: not knowing enough. There are so many ways to slice and dice data that it’s impossible to discuss 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 easier and will make you look like a guru. Bone up on any or all of these tricks to excel at Excel.
Select Everything, Everywhere All At Once
Yes, you can select all the data in the worksheet your looking at with a Ctrl-A. But you can also just click the little box at the upper left corner, above the one, left of the A column, to do the same.
Autofit All the Columns/Rows Instantly
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 could take a while. Instead, do a Ctrl+A to select all then tap Alt+HOI (hit the letters in that order). That will autofit all the columns. Then 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.
Drag That Data Around
Got a column you wish was 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 do it. 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 get to 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.
Combine Multiple Workbooks Into One
If you have a bunch of workbooks that are all formatted exactly the same, you could copy and paste them all into one. But when some Excel sheets have hundreds of thousands of rows, that’s precarious work. Instead, put all those files in the same folder. In Excel, 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 now 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. Wait a while; it 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.
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 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 just selecting Freeze Panes. This is where it gets 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 frozen cells, select Unfreeze Panes from the menu.
We hope these tips have been helpful in your business. Please let us know if you have any questions about your IT environment or how to secure it from outside cyber threats. We are here for you! Contact us at (732) 780-8615 or email at [email protected].
Eric Griffith, Excerpt from “38 Excel Tips for Becoming a Spreadsheet Pro”, pcmag.com, April 4th, 2024