No matter what industry you work in or what your role is, chances are you’ve worked with Microsoft Excel in your career. No matter whether you’re an accounting pro who couldn’t get your job done without it, or a marketing professional who only uses Excel once in a while to do your quarterly or even yearly reports, I hope each of you will learn something useful from the five Excel quick tips I will share with you today.
Excel Quick Tips #1: Adding Multiple Columns to Spreadsheet at Once
I’m sure we are all familiar with the insert button on the “Home” tab of the Excel ribbon that allows you to add individual columns to your spreadsheets fast. But what if you need to add many columns, not just one? Clicking that button over and over will quickly become time consuming and tedious. Luckily, there’s an easier way.
Here’s how it works:
- Highlight the same number of columns you’d like to insert into your spreadsheet. Hint: Start your selection with the column you would like the new ones to appear to the left of.
- Right click on the highlighted columns.
- Choose the “Insert” option on the drop-down menu.
Bonus Tip: The same procedure also works for adding new rows to your spreadsheet.
Excel Quick Tip #2: Dividing Text into Multiple Columns
As a marketing pro, this is one tip I couldn’t live without when scrubbing spreadsheets with raw data and preparing them for use in my reports. Often, one of my spreadsheets will list customer or prospect’s full name in a single cell, while others will break the name into two columns, usually First Name and Last Name. But to analyze the data, I need all my spreadsheets to use the same consistent formatting. Personally, I usually prefer the two-column approach. But how do I break the single Full Name column into two, you ask? Using “Text to Columns” is by far the easiest way to achieve this.
Here’s how it works:
- Decide how many sections you would like to break the column of data into.
- Insert the number of columns you will need to hold the new data. For instance, if you only want to break the column into two pieces, you must insert one column. If you want to break the data into three pieces, insert two columns, and so on. (If you need help to insert multiple columns at once, see Excel quick tip #1 above).
- Highlight the column of data you’d like to work with.
- Choose the “Data” tab on your Excel ribbon.
- Find the “Text to Columns” button and click it.
- A new window will open, giving you a variety of choices for working with your data. You can separate the values in the selected column by a delimiter (any single character you specify that exists in the column). The most frequently used delimiter choice is probably comma, because if you’re working in a CSV file, the comma is used to separate the data in each column. Also, it’s not uncommon to see spreadsheets that format a person’s name as “First Name, Last Name.”
If there is not a single character that neatly breaks the data where you would like, should choose at the second option, “Fixed Width.” This choice is especially useful for columns containing dates or pieces of information that are all the same length.
- Once you made your selection, click “Next.
- If you opted to separate your data using the “Delimiter” option, click the box next to the character you wish to use as your delimiter from the list in the window, or define your own using the “Other” box.
- You will see a preview of how Excel will split your data. If you are happy with it, click “Next.” On this screen, you will set the data type for each of your columns. Choose from “General”, “Text”, “Date” (with a drop-down menu where you can choose how you would like to format your date) or “do not import column (skip).” The last option is great if some data in the column you’re parsing is no longer useful to you and you would just like to get rid of it.
- If you chose the “Fixed Width” option, click “Next”. Here you will see a data preview that more than likely has some vertical lines with arrowheads pointing up. These denote where Excel thinks you may wish to separate your data.
- If what you see is correct, click “Next” to move on to setting your formatting options for each column as described above.
- If the arrows are not in the right place, follow the on-screen instructions to adjust them.
Excel Quick Tip #3: Flash Fill
Filling in data on a long spreadsheet can be time-consuming and tiresome. Flash Fill helps make this process easier. It pays attention to what you’re typing and how you format it. Using that information, it tries to finish filling in the data for you. This can really come in handy if you have the reverse situation of what I described I described in the Text to Column example above. Let’s say, you have a “Last Name” and “First Name” column in your spreadsheet that you would like to combine into one “Name” column. In cases like these, Flash Fill will quickly become your best friend.
Here’s how it works:
- If you don’t already have one, add a new column to hold the data you’re about to combine. (it should be next to the First Name and Last Name columns for Flash Fill to work best).
- Begin typing the information into the first few rows of your new column. Soon, you will see Microsoft Excel suggest entries for the rest of the rows in that column.
- To accept them, all you must do is press “Enter” on your keyboard.
- If the suggested data is not what you were hoping for, continue filling in a few more rows. The pattern may be difficult for the software to recognize.
- Go to the “Data” tab in your Excel ribbon and click the “Flash Fill” button. This will ask Excel to suggest options for you once again. To accept these, press “Enter.” Other instances in which Flash Fill can be very useful is in quickly editing the formatting of phone numbers and addresses.
Excel Tip #4: View Two Sheets from the Same Worksheet Side by Side
Often, it’s convenient to put spreadsheets that have a common theme or those you use for a common purpose in the same workbook. But sometimes, it can be annoying when you need to see data from multiple sheets in your workbook. Sure, you can flip back and forth between worksheets by clicking on the respective tabs at the bottom of the screen. But doing that constantly takes unnecessary time and effort. Instead, why not display the worksheets in two different windows so you can look at them side-by-side?
Here’s how it works:
- Click “View” at the top of your Excel ribbon.
- Choose “New Window.”
- When you do this, your existing workbook will open in an entirely new window.
So, you’re now free to move one of them to your second monitor, if you have it, or set up a split screen on a single monitor by following these steps.
- Make sure neither of the windows you want to display are maximized. If they are, click the “Maximize” button again to undo this.
- Click the top of the window you’d like to move, making it the active window.
- Press and hold the flying Windows key.
- Press the right or left arrow key on your keyboard, depending on which side of the screen you would like the window to move to.
- Your selected window should now move to the desired side of the screen, leaving the other side of the screen open, where you can see thumbnails of your other available Windows.
- Click the thumbnail of the window you would like to have occupy the other side of the screen. Just like that, you can now view two items side-by-side with no second monitor.
Excel Tip #5: Embed a Spreadsheet in a Report
Often, you want to show a spreadsheet or chart that you’ve created using Microsoft Excel in an important report. But obviously, you cannot create the report itself in Excel. After all, desktop publishing and word processing is not what Excel does best. For that, you will most likely want to use Microsoft Word. The good news is you can now embed a spreadsheet from Excel into a Microsoft Word document. That way, it’s easily accessible to your readers without them having to switch between files.
Here’s how it works:
- In your Excel spreadsheet, select the data you would like to embed. This can be the entire worksheet or part of it.
- With that highlighted, open the Word document you would like to embed your spreadsheet into.
- Place your cursor where you would like to put the data.
- In the “Home” section on your Microsoft Word ribbon, find the “Paste” button. Click the drop-down arrow next to it.
- In the menu of options that appears select “Paste Special.”
- A new pop-up window will open. Here you want to select the option “Microsoft Excel Worksheet Object.”
- Choose “Paste Link” and then click “OK.”
Ta-da, like magic, your data will now appear in your Microsoft Word report.
We hope these Microsoft Excel quick tips will help you navigate data sets and create the reports faster and easier than ever. Do you have a favorite tip out of the ones we shared here, or maybe another one that we missed? Let us know in the comments below. For more great Microsoft Excel tips, check out our past blogs or these websites.
