For a thorough study of Microsoft Excel will take a very long time. Most often, users initially master the basic functions of this application MS Office, and a detailed study of all its features occurs in the process of working with it. It is very important if you have any questions while working with Excel, try to find the information you need and make the spreadsheet as convenient as possible. MS Excel is a very powerful tool that takes into account almost all users needs when working with spreadsheets.
How to fix a row in Excel
How to fix a column in Excel for scrolling
How to merge cells in Excel
How to make a drop down list in Excel
How to add a chart to another chart in Excel
How to compare two tables in Excel
How to swap columns in Excel
How to print large Excel spreadsheet on one page
How to fix a row in Excel
When working with MS Excel spreadsheet, there are often situations when all rows of the table do not fit on the screen of the monitor. In this case, when you view the bottom rows of the table, the column names located at the very top of the table will not be visible. To see which column these data belong to, you have to scroll the entire table upward. This, of course, is very inconvenient. One way to solve this problem is to fix a string with the column names. The table headers will always be visible at the top of the screen when you scroll down the page. This is done so.
- Place the cursor in any cell in the table. In the main Excel menu, click the “View” tab and click “Freeze Panes”.
- In the menu that appears, select “Freeze Top Row”.
After these actions, the top line will always be visible when the table is scrolled down.
If you need to consolidate more than one row, click on the line under the anchored area, click on the link “Freeze Panes” and select “Freeze Panes” in the drop-down menu.
Now when you scroll the table, all the lines that are above the selected row will always be at the top of the screen.
If necessary, the rows can be removed in Excel. To do this, go to the “Freeze Panes” section and in the opened menu select the “Unfreeze Panes” section.
How to fix a column in Excel for scrolling
The fastening of the column when scrolling an Excel spreadsheet is useful when the table header is not on the top, but on the left. In order to fix the first column of the table, you need to open the “View” tab, in the “Window” block go to the “Freeze Panes” section and select the item “Freeze First Column” in the drop-down menu.
If you want to anchor two or more of the first columns, select the topmost cell of the column that follows the last anchored column. On the “View” tab in the “Window” block go to the “Freeze Panes” section and in the drop-down menu select the “Freeze Panes” item.
In both cases, you cannot remove the fastening of areas using hot keys, for example, Ctrl + Z. This can be done only by going to the “Freeze Panes” menu. If you insert a column in front of a fixed column, the added column will also be fixed.
How to merge cells in Excel
Why join cells in a table? This can be useful, for example, when creating a header common to multiple columns. It’s very easy to do this. Select the cells that you want to merge with the mouse, and in the main Excel menu on the “Home” tab, click the “Merge & Center” button.
There is another way to solve this problem.
- Select the cells you want to merge and right-click on them. In the menu that appears, go to the “Format Cells”.
- In the window that opens, go to the “Alignment” tab and select the “Merge cells” option.
Click “OK”.
The above methods work correctly for empty cells. But what if you want to merge cells with non-empty content? For example, in the table there are columns “Day”, “Month”, “Year” and you need to combine them into one cell named “Date”.
- Click on the empty cell. In the formula line write = CONCATENATE.
- Specify which cells to merge. To do this, put a parenthesis in the formula, click on the first merged cell, put a semicolon in the formula, click on the second cell, put a semicolon, click on the third cell and close the bracket.
- If you leave the formula as it is, the contents of the cells will be printed in a row, without spaces. In order to specify a sign for dividing cells, it should be written in quotation marks in the formula. It is also separated by a semicolon.
- Press Enter. In the first cell of the “Date” column, a glued date value appears.
- In order to display the glued date in the remaining cells of the “Date” column, it is not necessary for each cell to write a similar formula. It is enough just to pull down the bottom right corner of the first glued cell. The formula for each cell will be recalculated automatically.
If you want to copy the contents of these cells and place it in another place in the table, you need to use the “Paste Special” option to insert the contents of the column.
How to make a drop down list in Excel
What is a drop-down list in Excel? It is a cell, when selected; an arrow appears to the right of it. When you click on this arrow, the list of values for this cell falls out. The user can select one of them. In order to organize a drop-down list in a cell, you need to perform a number of simple actions.
- Place the cursor in the cell where the drop-down list will be located. On the “Data” tab, go to “Data Validation”.
- In the window that appears, select the data type “List” and in the “Source” field, list the possible cell values that will appear in the drop-down list via the semicolon. Click “OK”.
- After clicking on the triangle to the right of the cell, a drop-down list will appear.
Possible values from the list can be entered not only manually (using a semicolon for separation), but also specifying the range of cells in the form of a formula.
How to add a chart to another chart in Excel
Graphs and charts in Excel help to display table data more graphically. Often, charts are used in all kinds of reports. For greater clarity, it is common to combine different kinds of diagrams on the same chart. For example, a pie chart is combined with a histogram or a linear graph. This is done so.
- Based on the table with the original data, draw a general diagram (histogram, graph, etc.), as shown in the figure.
- If you want to display the dynamics of changing the values of one of the sectors on the same chart, right-click on it and select “Change Series Chart Type…” in the menu that appears.
- Select the chart type for the series and click OK. In this case, two diagrams will be displayed on the same figure. There are several such graphs in the graph.
How to compare two tables in Excel
Comparison of several tables – the process is not as simple as it might seem at first glance. You cannot just take a couple of buttons and see the differences in the two tables. There are several methods for solving this problem. Here are the simplest of them. Excel allows you to compare not only the tables located on one sheet of the book, but also the data on different sheets, and also to compare the tables in different Excel books.
Tables are located on one sheet
In order to compare the data of tables on one sheet, the tables must be synchronized, that is, have a similar structure.
We will consider the first table as the main table, and in the second table we will mark the cells that do not coincide. Simplify the example and comparison will produce only one column – “Shipping costs”.
- Select the whole range of cells of the compared column with the mouse. In the main Excel menu on the “Home” tab, click the “Conditional Formatting” button in the “Styles” pane. In the list that appears, select the line “Manage Rules”.
- Click the “New Rule…” button.
- In the window that appears, select the “Use a formula to determine with cells to format” section. In the field “Format Values …” write the formula to compare the first cells in the range using the “not equal” sign (<>). For this example, the formula will look like =$B$2<>$I$2. Apply absolute addressing to all cell addresses. To do this, select the formula and press F4 three times. The formula takes the form =B2<>I2. Then click the “Format” button.
- In the window that appears, go to the “Fill” tab and select a color that will be colored with mismatched cells. Click “OK”.
- Click “OK” in the “New Formatting Rule” window.
- In the “Conditional Formatting Rules Manager” window, click “OK”.
Cells with a mismatch in the second table will be marked in color.
If you want to select all the values that do not match in the tables, you should use another way of solving this problem.
- Select the areas of the tables that you want to compare.
- On the “Home” tab, click the “Conditional Formatting” button and select the “Highlight Cells Rules” section. In the drop-down menu, click the “Duplicate values …” item.
- In the window that appears, click “OK”.
As a result of these actions, in both tables, the matching values will be marked with color, and the color of the cells whose data does not match will remain unchanged.
In the last window, you can select not duplicate, but unique values, then the cells will be selected in color, the values of which do not coincide.
The tables are located on different sheets
If you want to compare the data of cells located on different sheets, you can use the simplest comparison formula, using the “Sheet” argument. For example, you need to compare the data of the tables located on the first and second sheets of the Excel workbook. Tables we take the same as in the previous example, but they will be located on different sheets. At the end of the first table, we make another “Comparison” column, where “FALSE” will be automatically displayed if the values of the cells of the two tables do not match, and “True” if they coincide.
Select the first cell of the “Comparison” column on the first sheet. In the formula line, write =B2=Sheet1!B2 (for this example). This formula indicates that you want to compare the contents of cell B2 on the first sheet with the contents of cell B2 on the second sheet. Press ENTER.
In order for the formula to be recalculated for the remaining cells of the column, draw the cell for which the formula was calculated for the right bottom corner.
The tables are located in different Excel books
If you want to compare the data of tables located in different Excel books, the sequence of actions will be the same as in the previous example. Provided that both books will be opened in one Excel window.
How to swap columns in Excel
You can swap columns in an Excel spreadsheet in several ways.
- Copying. Using the “Paste” command, insert an empty column to the left of the last column that you want to swap. Copy that column to the right and paste its contents into an empty column. The column that was copied is deleted. As a result, we get two columns that are reversed.
- Inset. Click on the right column to select its contents. Execute the command “Cut”. Click on the left column, which must be swapped with the right column, select it. In the menu that is called by the right mouse button, select the command “Insert Cut Cells”.
- Drag and drop with the mouse. Select the column that you want to move. Place the cursor on its border, press the Shift key and, without releasing it, drag the column to the desired location.
How to print large Excel spreadsheet on one page
To ensure that all the data of a large table fit on one sheet of paper, you need to reduce it. MS Excel tools allow you to do this automatically.
On the “Page Layout” tab, find the “Page Setup” block and click on the small arrow in the lower right corner of it.
In the menu that appears, go to the “Page” tab and mark the option “Fit to: “. Set the field values to width and height to 1.
Click the “Print Preview” button and see how the table printed on the sheet of paper will look. If you are satisfied with the appearance of the printed table, close the print preview window and click the “Print…” button.
If you urgently need to print a spreadsheet from an encrypted Excel file, but you cannot remember the password, do not despair. There are special programs for Excel password recovery.