7 Simple Excel Tips and Tricks

1. Simultaneously Format Multiple Pages

Sometimes when you’re formatting a worksheet in Excel, you know you’re going to want to format a second or third worksheet the exact same way. Doing that manually seems tedious and unnecessary – and it is! You can actually use Excel’s grouped worksheets feature to format multiple pages at once. First, Ctrl-click the tabs of the sheets you’d like to group together; they’ll all turn white. (Normally, only the worksheet you’re currently in would be white; the others would be gray.) Then, start applying the formatting you’d like.

While the sheets are grouped, any formatting you apply to one sheet will also apply to all the others. The same goes for any headings or data you enter. Just remember to ungroup – by clicking on them – so that you don’t accidentally insert or remove data once you’re done with the universal edits.

2. Add Multiple Rows or Columns at Once

Adding multiple rows and columns one at a time can get tedious. And that’s time you can save with this simple trick. Just drag your cursor and select – from the existing rows or columns – X number of rows or columns, X being the number of rows or columns you’d like to add. Then, right click the highlighted rows or columns. Finally, choose Insert from the drop down menu. This time, when new rows or columns are inserted above the row or to the left of the column you first selected, X number of rows or columns will appear, not just one. For example, if you want to add five rows, highlight five rows and then click insert. Five new rows will be added into your spreadsheet. (And the highlighted rows will remain untouched). This is helpful when adding three or four rows; it’s crucial when adding tens or hundreds!

3. Speedily Delete Blank Cells

At times, when you’re entering or pulling in data, some cells will be blank. For certain calculations – like average value – or for aesthetic appearances, you’ll need to delete these blank cells. The fastest way to do this is to choose the column from which you’d like to filter out blank cells, go to Data->Filter, and after the downward button shows, undo Select All and then pick up the last option, Blanks. All blank cells will show immediately. Go back to Home and click Delete directly, all of them will be removed. You just saved yourself a lot of hunting and pecking.

4. Remove Duplicates

Some large sets of data have duplicate content – for example, someone listed more than once on a mailing list. If you’d like to easily remove duplicates in this or other situations, simply highlight the row or column that you want to remove duplicates from. Then, go to the Data tab, and – under Tools – select “Remove Duplicates”. When the pop-up appears, confirm which data you’d like to work with. Then, select “Remove Duplicates.” You can use this feature to remove duplicates from one row or column, but you can also use this feature to remove an entire row or column based on a duplicate value in the dataset. So if you have three rows with Jane Doe’s information and you only need to see one, you can select the whole dataset and remove duplicates based on Jane Doe’s repeated email address, phone number, or whatever piece of data is consistent and redundant in the listings. Your final list will then have only unique individuals without any duplicates.

5. Split or Combine Cell Data

Have you ever had it happen where you have information in one cell that you’d rather have in two? The most basic example of this situation is the first and last names of individuals. You might have John Doe in Column A, but you’d like to have John in Column A and Doe in Column B. Or maybe you have jdoe@bigcompany.com in Column C and you’d like to have his company name in Column D. Thankfully, it’s easy to make this happen.

In either case, first highlight the column that has the data you’d like to split into two columns. In our examples, this would be Column A or Column C. Next, click on the Data tab and select “Text to Columns.” A menu box will with additional information will appear, asking you to choose either “Delimited” or “Fixed Width.” “Fixed Width” means you want to select the exact location on all the columns that you want the split to occur. For example, five spaces or characters in. “Delimited” means you want to break up the column based on specific characters – such as commas, spaces, or tabs. For our examples, we’d use “Delimited.” The tab, semi-colon, comma, space, or whatever else indicates where you want the split to occur is called the delimiter. In our first/last name example, it’s the space between the first and last name. In our email example, it’s the “@” sign in the email address and the “.” Before “com.” Once you choose a delimiter, Excel will show you a preview of what your new columns will look like. If you’re happy with the preview, click “Next.” (If not, go back and adjust the delimiter or fixed width until you are.) Once you’ve clicked “Next”, you’ll have the option to select Advanced Formats if you choose. When you’re done, simply click “Finish.”

But what if you have the reverse problem – the data is separate and you want it together? This would mean you have the first name in Column A and last name in Column B, but you’d like the full name all in the same column. Excel makes this easy. First, put our cursor in the blank cell where we want the combined full name to appear. Type an “=” sign. Then, highlight the cell that contains the first name. (If the cell is A1, then “A1” will appear after the “=” sign). Next, type an “&” sign. Then highlight the cell with the corresponding last name. If you keep the formula like this and hit return, you’ll get the first and last name in the same cell, but there won’t be a space between the two names. If you’d like a space, add it in the formula like so: =A1&” “&B1. Using the quotation marks lets Excel know to put a space between the data from the first column and the data from the second.

6. Transpose

Have you listed stuff in a row when you really want it in a column? Or vice versa? By the time you realize you’d rather have it running on the other access, it’s going to take a lot of time to make the switch. But that doesn’t have to be the case. To transpose your data, first highlight the column that you want to transpose into rows. Right-click it, and select “Copy.” Second, select the cells on your spreadsheet where you want your first row to begin. Then, right-click on that cell and select “Paste Special.” Inside the menu box that appears, you’ll see an option to Transpose. Check the box next to that option and select “Ok.” Your column will now be transferred to a row. If you’d like to turn a row into a column, follow the same directions, but replace the word “row” with “column” and vice versa.

7. Conditional Formatting Formula

Do you ever want your formatting to reflect your data? Maybe you want all the rush orders to be red, all the vegetarians to be green, or all the numbers in the 90th percentile to be orange. You can make this happen easily using conditional formatting. First, highlight the group of cells you want to use conditional formatting on – for example, the whole column in which diners are listed as vegetarian, vegan or carnivorous. Second, choose “Conditional Formatting” from the Home menu and make your choices. If you don’t see the logic listed that you’d like to use, you can create your own rule. A window will pop up that asks you to provide more information about your formatting rule. When you’re done, select “OK”. Your results will automatically appear and your data will be easier to read and analyze.

Written by DSD Business Systems