Microsoft Excel can be used in almost any industry. It’s a staple when it comes to keeping organized and communicating information effectively.
You don’t have to be an Excel expert to start saving time on assignments which involve spreadsheets. The best way to learn Excel and appreciate the efficiency that’s inherent in the program is to try out a few tricks for yourself. The more you try some simple Excel tips and tricks, the more comfortable you’ll be using advanced Excel tricks moving forward.
In addition to helping you in college, these Excel tips and tricks can help you secure and retain a job after you graduate. In 2013, IDC published a white paper which was originally commissioned by Microsoft Corporation. The study revealed the skills which students need to gain employment in the top 60 high-growth, high-compensation fields. These professions will be responsible for creating approximately 11.5 million new positions, which equates to 28 percent of overall job growth, between 2013 and 2020. Proficiency in Microsoft Office is a requirement for 15 percent of the positions which will be created. In fact, an adeptness with Microsoft Office is so coveted by employers that it ranked third in the list of the top 20 skills companies require people to have for them to be considered for employment.
Here are some Excel tips and tricks that will benefit you throughout your college years and professional vocation:
Freeze Rows and/or Columns: It can be difficult to work on a large spreadsheet when you can’t see the heading for a given row or column. It’s not efficient for you to constantly scroll up or to the left to see what type of information a cell requires or is providing. Depending on the size of your monitor, that’s exactly what you’d have to do if you have just 25 rows or 21 columns in your spreadsheet.
To avoid having to scroll up, down, and sideways unnecessarily, you can freeze rows at the top of your spreadsheet. Excel also enables you to freeze columns on the left side of your spreadsheet. By freezing rows and columns, they’ll be within your view no matter what row or column you’re working in. If you freeze the headings in row three and column A, for example, you’ll be able to see them even if you’re working in column BW and row 1,537.
To freeze rows and columns, navigate to the cell which is at the intersection of the particular row and column you want to keep in view at all times. Once you’re there, click on View at the top of your screen. Next, click on Freeze Panes.
Navigate Quickly: Even if your spreadsheet isn’t huge, it can be time-consuming when you have to scroll to the end of a row or column to enter new data. There are two ways you can save time by getting to the end of a row or column without having to manually scroll. One way is to press your End key and use the up, down, right, or left arrow keys in the lower right-hand corner of your keyboard. The alternative is to press Control and use your directional keys to get to where you want to be.
If you want to move to the beginning of your spreadsheet quickly, you can get to home by pressing your Control and Home keys at the same time. If you’ve frozen rows and columns, pressing those keys will put you at the cell where your frozen row and column intersect.
Add Multiple Columns or Rows: In certain instances, you may have to add a few rows and columns to accommodate all of your data. In other cases, you may have to add a lot of rows and columns. Adding rows and columns one by one is not only tedious, but it can also be time-consuming when you have to add tens, hundreds, or thousands of rows and columns.
You can add one new column quickly by pressing your Control, Shift, and Equal keys simultaneously. If you want to add multiple rows or columns at the same time, doing so is just as easy. Simply highlight the number of rows or columns already included in your spreadsheet that you want to add, right-click, and select Insert from the dropdown menu.
Delete Blank Cells: Sometimes it’s necessary to remove empty cells from a row or column. This is particularly true if you’re working on a project which involves finding the average value of a row or column. If you’re looking for the average value of the first thirty rows in column C, for example, and ten of the cells are blank, your result will be different if you used all thirty cells in your calculation than it would be if the blank cells were eliminated.
To remove blank cells from a row or column, select the row or column from which you want to remove blanks and press your Control and G keys. Select “Special” in the Go To dialogue box, click Blanks, and then click Okay.
Spell Check: Just as the numbers you put in your spreadsheet need to be accurate, so do the words you use to label your data. You can spell check your entire document by pressing the F7 key. If you only want to check certain rows and columns, highlight them before you press F7. If your project involves more than one spreadsheet, you can spell check them all at once by grouping them prior to pressing F7.
Apply Formatting Repeatedly: Beginning with Excel 2007, newer versions of the program have allowed you to use the Format Painter more than once without having to click the paint brush icon repeatedly. You can use the Format Painter an unlimited number of times in a row just by double-clicking it. When you’re done applying that style, press your Escape key.
Repeat Print Titles: If your assignment involves multiple Excel pages, you can save time by automatically repeating your print titles on each page instead of manually typing them on every page. To do this, click on the Page Layout tab at the top of your screen and then click Print Titles. In the Page Setup dialogue box that will open, enter the rows and columns you want to repeat on each page in the boxes labeled “Rows to repeat at top” and “Columns to repeat at left,” respectively.
Skip the = Key: Depending on your familiarity with Excel, you may be conditioned to use your Equal key every time you enter a new formula. If you want to save some time, you can skip using that key when you’re entering a formula which begins with either a plus or a minus sign. If your formula begins with a plus or minus sign, Excel will add the equal sign to the beginning of your calculation when you press enter.
Repeat Formula: You can save even more time by only entering the formula you need to use throughout a given column in the first cell of that column only. To populate the rest of the column with your formula, move your cursor to the lower right-hand corner of the cell occupied by your calculation. When your cursor looks like a plus sign or cross, double-click it. Your formula will then be copied into the remaining cells in that column, but the formulas will self-adjust themselves to pull data from the relevant row.
Copy Patterns/Autofill: Excel is programmed to recognized patterns that are apparent in your data. This gives you the ability to copy your data to other cells instead of having to re-enter it. To do this, establish your pattern by entering information in two rows or columns. Highlight the populated rows or columns and then drag down to fill the number of rows or columns you want to populate beneath or beside them. You can also populate a range automatically by double-clicking your cursor when it appears as a plus sign above the bottom right corner of the occupied cell. Doing this will fill the adjacent range.
Combine Text from Different Cells: In certain cases, such as when you’re working with first and last names, you may want to combine text from different cells so that it displays together in a single cell. You can do this by using an ampersand in your formula. For example, if you have a person’s first name in row three of column C and the individual’s surname in row three of column D, you can display the person’s first and last names in column E by using the following formula: =C3&D3. To put a space between the individual’s first and last names, revise your formula to look like this: =C3&” “&D3.”
Use Text to Columns: Conversely, if you need to separate text in a cell into multiple cells, Excel enables you to do that as well. If you’re still working with first and last names, for instance, and you need to put a person’s first name in one cell and the individual’s last name in a different cell, start by selecting the data cells you want to extract different pieces of text from. Click on Data, Text to Columns, Delimited, and then Next. Choose the option you want to use to separate your data under Delimiters or designate one in the box next to Other. Click Next and then choose the format you want to use and the destination where you want your separated data to appear. Once you’re done with that, click Finish.
Use Carriage Return in a Cell: If you want to use a carriage return in a cell instead of wrapping the text which is in that field, Excel will let you do just that! To insert a carriage return, simply press your ALT and Enter keys at the same time.
Select Tab Colors: If you’re working with multiple spreadsheets in a workbook, you may have to refer to some of them more often than you look at others. Color-coding your most-used spreadsheets can help you to get to the information you need faster than you would if all of your sheet tabs look alike. You can color a sheet tab by right-clicking on the tab at the bottom of your screen. Choose Tab Color from the list that appears and then select the color you want the tab to be shaded.
Move Between Spreadsheets: When you want to get from one spreadsheet to another, you can do so without having to use your cursor. To move to a spreadsheet on the left of your screen, press your Control and Page Up keys. To move to the right, press Control and Page Down.
Open Multiple Files at Once: When you have to work with multiple Excel files simultaneously, you can save time by opening them all at once instead of one at a time. If all of your files are in a row, click on the first one you want to open while holding down your Shift key and then click on the last one you want to open. Click Open and all of the files you selected will open. If your files are not in a list, you can select the ones you want to open individually by holding down your Control key and clicking the names of the files you want to open. Each file you click will be highlighted, and every selected file will open when you click Open.
Navigate between Files: If you have different Excel files open at the same time, you can move between them just as easily as you can move from one spreadsheet to another in a workbook. Simply press your Control and Tab keys to navigate to another file.
Synchronize Scrolling: If you’re working with two spreadsheets related to two different companies, you may want to compare a piece of data which can be found in the same location on both spreadsheets. To get to that information in both spreadsheets at the same time, you can synchronize your scroll. For Excel 7 and more recent versions of the program, click View at the top of your screen. Then, click on View Side by Side to pair the spreadsheet you’re currently looking at with the one you want to compare it to. Finally, click on Synchronous Scrolling.
Transpose Rows and Columns: To prevent you from wasting time by retyping data in your spreadsheet, Excel enables you move the information in a row into a column or vice versa when you want to do so. To transpose data, copy the data you want to reposition and move your cursor to the cell in which you want to start displaying that information. Click home in the upper left-hand corner of your screen, click Paste Special, and then click Transpose.
Customize Your Shortcut Options: Excel’s shortcut menu comes pre-programmed with certain options, including Save, Undo Typing, and Redo Typing, but the program enables you to customize your shortcut menu to suit your needs. To make additions to your shortcut menu or to remove options you don’t use often, click File, drag down to Options, and click on Quick Access Toolbar. The things you already have on your shortcut menu will appear on the right-hand side, while the options you can add to them will be listed on the left. Choose the actions you want to add to your toolbar by selecting them from the list on the left and dragging them to the list on the right. To remove things from your shortcut menu, select them in the list on the right and drag them to the list on the left. Click Okay when you are finished.
The next time you have an assignment involving Excel, utilize these Excel tips and tricks to simplify the process and save time!