How to work with Excel

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.

  1. Place the cursor in any cell in the table. In the main Excel menu, click the “View” tab and click “Freeze Panes”.Freeze a row in Excel
  2. In the menu that appears, select “Freeze Top Row”.Lock the top line Excel

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. Freeze Excel String
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.Unpin Excel Strings

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. Freeze an Excel column
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.Freeze Excel columns
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.Merge Excel cells
There is another way to solve this problem.

  1. Select the cells you want to merge and right-click on them. In the menu that appears, go to the “Format Cells”.Merge Excel cells
  2. In the window that opens, go to the “Alignment” tab and select the “Merge cells” option.Merge multiple Excel cells 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”.

  1. Click on the empty cell. In the formula line write = CONCATENATE.Interconnect Excel cells
  2. 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. Function CONCATENATE Excel
  3. 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.Interconnect Excel cells
  4. Press Enter. In the first cell of the “Date” column, a glued date value appears.Glue several Excel cells
  5. 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.

  1. Place the cursor in the cell where the drop-down list will be located. On the “Data” tab, go to “Data Validation”.Excel drop down list
  2. 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”. Make an Excel drop-down list
  3. After clicking on the triangle to the right of the cell, a drop-down list will appear.Excel drop down list

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.

  1. Based on the table with the original data, draw a general diagram (histogram, graph, etc.), as shown in the figure.Make an Excel chart
  2. 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.Make a graph in the Excel chart
  3. 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. Graph in Excel chart

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. Compare Excel Sheets
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”.

  1. 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”.Compare two Excel spreadsheets
  2. Click the “New Rule…” button.
  3. 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.
  4. In the window that appears, go to the “Fill” tab and select a color that will be colored with mismatched cells. Click “OK”.
  5. Click “OK” in the “New Formatting Rule” window.
  6. In the “Conditional Formatting Rules Manager” window, click “OK”.Find differences in Excel spreadsheets

Cells with a mismatch in the second table will be marked in color. Mark color differences in Excel spreadsheets
If you want to select all the values that do not match in the tables, you should use another way of solving this problem.

  1. Select the areas of the tables that you want to compare. Find mismatches in Excel spreadsheets
  2. 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.Find duplicate values in Excel spreadsheets
  3. 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. Highlight differences in Excel tables
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. Compare tables on different Excel sheets
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. Compare two tables on different Excel sheets

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. Compare tables in different Excel files

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. Print the table on one Excel sheet
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. Excel print a spreadsheet on one sheet
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.