Excel tips
Work With Cells Across Sheets
When you have multiple sheets in a workbook that all have the same essential layout (i.e., financial statements or invoices), you can use the 3D Sum to work with corresponding cells across different sheets. Create a new worksheet within your workbook, then pick a cell to type a formula such as = SUM(‘W1:W8’!A3), which will total up cell A3 from sheets labeled between W1 and W8. This helps when you want to create a master spreadsheet that tracks the data in your workbook as it evolves.
Save Charts As Templates
Excel has tons of chart formats to choose from, but sometimes you need an extremely customized one to suit your business or presentation needs. Thankfully, Excel has an equally impressive ability to allow you to customize charts and graphs. To avoid the painful task of recreating your very specific template, right-click on your tweaked and tuned chart and select “Save as template.” Be sure to save a file with a CTRX extension in your default Excel templates folder. To access your template for later use, select the data you want to chart, go to the “Insert” tab and click “Recommended charts.” Then under the “All charts” tab, locate the “Template” folder, and in the “My Templates” box, pick the one you’d like to apply, then click OK. Be sure to note that some elements, like the actual text in legends and titles, won’t carry over unless they’re part of the data set, but font and color selections, embedded graphics, and series options will carry over.
Paste Special
When you find yourself needing to turn a bunch of rows into columns or vice versa, the paste special function is your best friend. Rather than moving data cell by cell, highlight and copy the data, right click and select “Paste special,” check the “Transpose” box, then click “OK” to paste the data into your desired orientation.
Multiple Cells, Same Data
Another handy Excel tip when working with a large set of information allows you to de-duplicate data and ensure you’re left with a unique set of values without repetition. First highlight your list, then choose “Remove duplicates” from the “Data” tab. From the columns you’ve selected, the menu will prompt you to select which columns that you want to remove duplicates from.
Text To Columns
If you’re importing data from another program or re-formatting a spreadsheet, you may encounter a situation where you have a list of data in one column (i.e., first and last names) and need to break it out into two. To do this, select the data, then click “Text to columns” in the “Data” tab. This allows you to swiftly separate the data by delimiters like commas, or fixed width like a number of spaces or a period.
Flash Fill
Similarly, the flash fill feature populates columns based on a pattern of data it sees in the first row. By the second row, Excel should begin to recognize the pattern and will provide a suggestion of data and format to fill out further cells and you only need to hit enter to use it. If it doesn’t give you an accurate range, keep entering data until Excel catches on. Finally, go to the “Data” tab at the top of the spreadsheet and hit “Flash fill” to populate the rest of the sheet. Flash fill works with values like numbers, names, and dates, and is most efficient when the data is organized by a top header row.
Auto Fill
Excel has built in features to help users quickly fill in the data itself. The first of the two we’ll cover here is auto fill, which helps you to avoid spending an afternoon typing a repetitive series like a list of dates or numbers into a spreadsheet. To activate auto fill, begin your series just enough to establish a pattern, then move the cursor to the lower right corner of the last filled out cell. The cursor should automatically turn into a plus sign. Once it does, drag down to select all the cells you need to fill and Excel will do it for you following the pattern you started. This also works if you need to fill up a column or left to right rows. It’s possible to use auto fill without a pattern. Pick a cell or group of cells, move the cursor to the plus sign position, click, drag, and you’ll be provided a menu of options that allow you to dictate the data that’s auto-filled. The more data you’ve entered, the better tailored the options in the menu.
Ctrl+Shift
Add the Ctrl+Shift function to your arsenal for another way to quickly select large data sets. First click the first cell you want to highlight, then hold down Ctrl+Shift. From there, hit the down arrow on the keyboard to get highlight the data below the cell, the up arrow to highlight above, or the left and right arrows to highlight the rows on either side. For even larger data selection, hold down Ctrl+Shift+End to make the cursor jump to the lowest right-hand cell with data while selecting everything in between, or hit “Ctrl+Shift+* to highlight the whole data set no matter which cell you clicked first.
Shift Between Workbooks
When working across multiple workbooks, you’ll be very thankful for this Excel trick that allows you to hop between documents and avoid messing up an entire project by one mis-entry into the wrong workbook. Just hit Ctrl+Tab to quickly and efficiently shift between workbooks.
Familiarize yourself with keyboard shortcuts
Alright, perhaps this first one doesn’t count as just one hack. However, if you’re looking for a surefire way to save yourself some time and frustration in Excel, it pays to familiarize yourself with some different keyboard shortcuts. There are hundreds (yes, literally hundreds) that you can use. So, the best thing to do is to take note of some of the common tasks or actions you’re taking in Excel, and then seeing if there are existing keyboard shortcuts for those. Want a handy resource? We already pulled together a list of 200 Excel shortcuts for PC and Mac that you can reference! Download your free shortcuts PDF cheat sheet below. Here are a few of our favorites: