Microsoft Excel is commonly used by students when they need to optimize complex problems, perform some basic calculations or manage certain data. However, the accuracy of each of these tasks depends a lot on student’s expertise in using this software. Therefore, because of the insufficient knowledge of MS Excel, they sometimes fail in composing high-scoring assignments. It should be mentioned that MS Excel can be used not only in education, but almost in every industry. It is one of the best examples of software that helps keep organized and communicating information efficiently.
There is no necessity to be an Excel expert to save your time on the assignments that require work with spreadsheets. The best and most effective way to learn this program and realize how much it can increase your productivity is to try out some excel tips for yourself. The more you use tips for excel, the more comfortable you will feel when it comes to organizing data. So, here are some best Excel tricks, which will benefit you during your college and professional career.
Freeze Rows and/or Columns
Often, it is difficult to work on a big spreadsheet if you cannot see the heading for a certain row or column. It is not effective to scroll up or to the left every time to check which kind of information a cell provides or requires. There is one useful function in MS Excel that allows avoiding having to scroll up, down, and sideways unnecessarily. Instead, you can simply freeze rows at the top of the spreadsheet or columns on its left side. Due to this function, rows and columns will be always within your view. For example, if you freeze the headings in row 2 and column B, you will be able to see them even while working in column CX and row 2,648. To activate this function, navigate to the cell that is located at the intersection of a certain row and column that should be kept in view. When you are there, click on View at the top of the screen, and then on Freeze Panes.
Even if you are working with quite a little spreadsheet, it consumes extra time to scroll to the end of a row or column for typing new data. However, due to some Microsoft Excel tips, you have an ability to get to the end of a row or column without manual scrolling. You can just press the End key and use the directional keys on the keyboard. Or, press Control and use the directional keys to get to the required cell. For quick moving to the beginning of the spreadsheet, press Control and Home keys simultaneously.
Add Multiple Columns or Rows
Sometimes there occurs a need to add several or multiple rows and columns to accommodate all of the data. Adding them one by one is tiresome, ineffective and requires much time. But you can save much time adding one new column by pressing Control, Shift, and = keys at the same time. If it is necessary to add a lot of rows or columns, doing so it is very easy. Just highlight the amount of rows or columns already included in the spreadsheet that you have to add, right-click, and select Insert.
Delete Blank Cells
Sometimes, there is a need to delete empty cells from rows or columns in excel projects for high school students. In particular, this is useful when you work with projects where you have to find the average value of a row or column. For instance, if you have to find an average value of the first twenty rows in column B, and five of the cells are blank, you will get different results if you use all twenty cells in the calculation and if you eliminate the blank cells. To delete empty cells from a column or row, select the one from which you need to remove banks and press Control and G keys. Select “Special”, click Blanks, and then Okay.
You certainly want each word you use to be accurate and clear. To ensure everything is fine, just press the F7 key. If there is a need to check certain rows and columns, highlight them prior to pressing F7. If you need to check several spreadsheets at once, group them before pressing F7.
Apply Formatting Repeatedly
Starting with Excel 2007, every new version of the program allows a user to use the Format Painter for multiple times without having to click the paintbrush icon time after time. It enables you to use the Format Painter an unbounded number of times in a row. To do so, you have to double-click it. When you have finished with applying the required style, press the Escape key.
Repeat Print Titles
If there are numerous Excel pages in your assignment, you can use the ability to automatically repeat the print titles on every page rather than manually type them on each one. You just have to click on the Page Layout tab, which is located at the top of the screen, and then choose Print Titles. Then, in the Setup dialogue box, you should enter the rows and columns that should be repeated on every page.
Skip the = Key
Depending on how well you are familiar with Excel, you may be using the Equal key in your excel homework assignments each time for entering a new formula. There is a function that allows you to skip using the = key when entering the formula, which has either + or – sign at the beginning. In such cases, Excel adds the = sign to the beginning of your calculation once you press the Enter key.
There is also a great option that allows you to only enter the formula required to use throughout a certain column only in the first cell of that column. To repeat it in the rest of the column, move the cursor to the lower right-hand corner of the cell with the entered formula, and double-click it when it looks like a plus sign. As a result, the formula will be occupied into the rest of the cells in the column, but the formulas will be self-adjusted to pull data from the relevant row.
Microsoft Excel recognizes patterns, which are obvious in the data. Due to it, you can copy the data to other cells rather than re-entering it. To establish the pattern, you have to enter certain information in two rows or columns. When it is done, highlight them and drag down to fill the rows or columns, which have to be populated beneath or beside them. Also, a range can be populated automatically: just double-click the cursor when it appears as a + sign above the bottom right corner of the occupied cell.
Combine Text from Different Cells
Sometimes, for example, when working with names, there occurs a need to combine text from various cells so that it is displayed together in one cell. It can be easily done by using an ampersand in the formula. For instance, if you have somebody’s name in row five of column E and his or her surname in row five of column F, you can display one’s first and last names in column G by using formula =E5&F5. If the space is required between the name and surname, revise the formula to look as the following: =E5&” “&F5.” This is one of the helpful tips for Microsoft Excel.
Use Text to Columns
When you need to divide text in one cell into several cells, the programs has a special function for this, as well. Let’s look at the example with names and surnames again. If you want to put the name in one cell and surname in another one, first, select the data cells from which you want to extract certain pieces of text. Then, choose Data, Text to Columns, Delimited, and then Next. After it, select the required option to separate the data under Delimiters or designate one in the box next to Other. Click Next and select the required format and the destination where the separated data should appear. In the end, click Finish.
Select Tab Colors
There are multiple tips and tricks for Excel, which you may want to use. For example, when you are working in a lot of spreadsheets in a workbook, you may have a need to refer to certain ones more often that to others. To get the required data, you may like to color-code the most-used spreadsheets. It can be done by right-clicking on the tab at the bottom of the screen. Select Tab Color from the list, which appears, and choose the color, which you want for a certain tab.
Open Multiple Files at Once
If you need to work with several Excel files at the same time, you can open them all at once rather than opening them one at a time. If all the required files are in a list, choose the first one that needs to be open while holding the Shift key, and then choose the last one that needs to be open. Click Open and all of the chosen files will open. If the needed files are not in a list, click on the ones, which have to be opened individually, by holding the Control key. Each file you click will be highlighted. Then click Open to open the chosen files.
While working with two spreadsheets related to various companies, you may need to compare some pieces of information that are placed in the same location on both spreadsheets. If you want to get to that data simultaneously, just synchronize your scroll. If you are using Excel 7 or a newer version, click View at the top of the screen. Then, choose View Side by Side to pair the spreadsheet with which you are currently working and the one you need to compare it to. When it is done, click on Synchronous Scrolling.
Transpose Rows and Columns
To avoid retyping data, you can use the ability to move data typed in a row into a column and vice versa when it is needed. For transposing data, copy it and move the cursor to the cell, in which you want to display it. Click home in the upper left-hand corner of the screen, then Paste Special and finally Transpose.
Customize Your Shortcut Options
The shortcut menu of the program has certain pre-programmed options, such as Save, Undo Typing, and Redo Typing. However, there is also a possibility to customize your own shortcut menu with the required options only. To add or remove certain things in the menu, click File, drag down to Options, and click on Quick Access Toolbar. Those options you already have on the shortcut menu on the right side, and the ones, which can be added to them, will be listed on the left side. Select the actions you want to add to the toolbar by selecting them on the left list and dragging them to the right list. To remove them from the shortcut menu, conversely, choose them in the right list and drag to the left one. Click Okay when you are finished.