MS Excel is designed to work with data placed in tables. It provides the user with a huge number of features, which not everyone knows about. In this article, we briefly tell beginners to learn Excel how to use only part of its many functions.
Creating and formatting tables
The main tasks of the program are storing data in tables, performing calculations on numerical values, generating reports and statistics. Therefore, the first step to be taken is to create a table on an Excel worksheet.
When you start the program, an empty sheet is automatically opened. You need to enter your data into it - create a table. The first line contains headers, and the next line contains values. For example, performance data for high school students.
To format the table data, use the buttons on the Excel Home tab. Using the button commands, the headers are highlighted in bold, the color is filled, the text in the cells is aligned and other actions are taken to make the table look better and the data in it is easier to read.
You can refer to ready-made styles in Excel. How to use styles and formatting tables? Select the entire table with the mouse cursor, open the Styles toolbar and select one of the menu items on it: “Format as table” or “Cell styles”. The first of the elements will allow you to apply the selected style to all cells of the table, the second - only to selected ones. A formatted table is easier to read and provides more tools for working with data.
Excel Filtering
One of the features of a formatted table is working with filters. The photo below shows how to use the filter in Excel from the drop-down list of headers.
The selection is carried out both by values and by condition depending on the type of data in the column. For example, numerical filters allow you to select values more-less or in a specified range of numbers. Text selections look for occurrences of a given row at the beginning, end, or arbitrary place of data in a cell.
To perform a search using two criteria, you can configure the filter settings. To do this, select the menu item "Custom Filter". In the settings window, you select two conditions, connecting them together with the value "And" or "Or."
In addition to filtering by value, you can select data by cell color or font. In formatted tables, such filters do not work, because the colors are determined by the style of the table.
But sometimes you need to do data filtering in an unformatted Excel spreadsheet. How to use the filter in this case? Select the desired column and select the "Filter" menu item of the "Sort and Filter" toolbar. A drop-down list already familiar to us appears in the column heading.
Excel Formulas
To perform calculations, the program provides formulas. How to use formulas in Excel ?. Calculate the average grade for each student. To do this, add another column “Average rating” to the table. In the upper cell of the column, make the calculations.
To add a formula, an equal sign “=” is written in the cell. After it, the arithmetic operations to be performed are indicated. Links are used to use data in other cells. For a formatted table, the formula will look like this:
= ([@ Physics] + [@ [Russian language]] + [@ Chemistry] + [@ History] + [@ Informatics] + [@ Literature]) / 6
In order not to write down all the terms, you can use one of the many functions - SUM in Excel. How to use this function, let's look at an example.
And finally, in order not to manually calculate the number of items to calculate the average score, we use the ACCOUNT function.
The information in the article helped you understand how to use Excel, where to start, and how some of the program’s functions work.