You’ll often find it handy to be able to select all of the data in your spreadsheet, and clicking each cell one-by-one just won’t cut it when moving and formatting large quantities of data. Instead, either hit Ctrl+A or click the arrow button at the top corner of the spreadsheet to select all in seconds.
As you build and add to your spreadsheet, it’s essential to be able to add multiple rows or columns at once rather than going the manual route. To start, pick the area where you want to add a new batch of rows or columns, then click and drag the highlight over the number of lines you want to add (e.g., highlight seven rows to add seven). Right-click and choose “Insert” from the drop down menu to insert rows above the row or to the left of the column you selected.
Despite Excel’s many capabilities, the simple task of typing in and formatting spreadsheet cells can be frustrating since text defaults to continuing in one line. Luckily that is easily fixed by holding down Alt+Enter to create a new line as you type. You can also click “Wrap text” in the top menu to have the text automatically wrap at the edge of the cell you’re in, and adjust as you resize the column or row.
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.
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.
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.
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.
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.
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.
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.