Simple instructions for beginners with formulas and more

Microsoft Excel table calculation basics

Sometimes Excel seems too good to be true. Do data have to combine in several cells? Excel can do it. Do you have to copy the formatting via a number of cells? Excel can do that too.

Insert lines or columns

If you work with data, you may need to add further lines and columns. To do this after another would be super boring. Fortunately, there is an easier way.

To add several lines or columns in a table, mark the number of existing lines or columns that you want to add. Then right -click the mouse button and select “Insert”.

In this example, I add three lines to the top of my table.

Car

With Autofill you can quickly fill neighboring cells with different types of data, including values, series and formulas.

There are many ways to provide this function, but the filler handle is one of the easiest.

First select the cells you want to be of the source. Next, find the filling handle in the lower right corner of the cell. Then either pull the filling handle to cover the cells you want to fill, or simply double -click.

filter

If you are looking for large data records, you don’t usually have to look at every line at the same time. Sometimes you only want to look at data that fit in certain criteria. Here filters come into play.

With filters you can park data to see only certain lines at the same time. In Excel you can add a filter to any column in your data. From there you can choose which cells you want to display.

To add a filter, click the Data tab and select “Filter”. Next, click on the arrow next to the column headings. In this way, you can choose whether you want to organize your data in ascending or descending order, as well as which lines you want to display.

Let’s take a look at the example of the Harry Potter example. Say you just want to see the students in Gryffindor. By selecting the Gryffindor filter, the other lines disappear.

Pro tip: Start with a filtered view in your original table. Then copy the values ​​and add the values ​​to another table before you start analysis.

Sort

Sometimes you have an unorganized list of data. This is typical if you export lists, e.g. B. Marketing contacts or blog posts. Excel’s sorting function can help you to literate every list.

Click the data in the column you want to sort. Then click on the “Data” tab in your toolbar and search “Sort” on the left for the option.

• If the “A” is above the “Z”, you can simply click on this button. The selection of AZ means that the list is sorted in alphabetical order.
• When the “Z” is above the “A”, click the button twice. The ZA selection means that the list is sorted in reverse alphabetical order.

Remove duplicates

Large data records tend to have double content. For example, you may have a list of different company contacts, but only want to see the number of companies you have. In such situations, removing duplicates is useful.

To remove duplicates, mark the line or column in which you have noticed double data. Then go to the data tab and select “Remove Duplicates” (under Tools). A popup is displayed so that you can confirm which data you want to store. Select “Remove duplicates” and you can get started.

If you want to see an example, this article offers step-by-step instructions for removing duplicates.

You can also use this function to remove an entire line based on a double column value. Suppose you have three information lines and only have to see one. You can select the entire data record and then remove duplicates. The resulting list only has clear data without duplicates.

Insert special

It is often helpful to change the elements in a series of data to a column (or vice versa). It would take a lot of time to copy and insert every single header.

Create a new workbook

PC: Ctrl-N | Mac: command n

Select the entire line

PC: switching room | Mac: circuit room

Choose the entire column

PC: Ctrl room | Mac: control room

Choose the rest of the column

PC: Ctrl-Shift-Down/Up | MAC: Command shift/up

Select the rest of the line

PC: Ctrl-shift rights/left | Mac: Command switching rights/left

Add hyperlink

PC: Ctrl-K | Mac: Kommando-K

Open the cell windows for shape plates

PC: Ctrl-1 | Mac: Kommando-1

Autosum selected cells

PC: old = | Mac: Command shift-t

Excel formulas

At this point, you get used to the surface of Excels and fly through fast commands in your spreadsheet.

• Use the + character to add.
• Use the – sign to subtract.
• Use the * sign to multiply.
• Use the / sign to share.
• Use the ^ sign to use exponents.

Remember that all formulas in Excel have to start with an equal sign (=). Use brackets to ensure that certain calculations are made first. For example, look at how = 10+10*10 from = (10+10)*10.

Apart from the manual input of simple calculations, you can also refer to the integrated formulas of Excel. Some of the most common are:

• Average: = Average (cell area)
• Sum: = Sum (cell area)
• To count: = Count (cell area)

Also note that the series of certain cells are separated by a comma (,), while the cell areas are noted with a large intestine (:). For example, you can use one of these formulas:

• = Sum (4.4)
• = Sum (A4, B4)
• = Sum (A4: B4)

Conditional formatting

With conditional formatting, you can change the color of a cell based on the information in the cell. Suppose you want to mark a category in your table.

Mark the group of cells to use them to use. Then choose “conditional formatting” from the home menu. Then select a logic option from the dropdown -downart. A window is displayed in which you provide further information about your formatting rule. Choose “OK” when you’re done and you should see your results automatically displayed.

Note: You can also create your own logic if you want something beyond the dropdown selection.

Dollar signs

Have you ever seen a dollar sign in an Excel formula? When this symbol is in a formula, it does not represent an American dollar. Instead, she ensures that the exact column and the line remain the same, even if you copy the same formula in neighboring lines.

You see a cell reference – if you refer to cell A5 from cell C5, for example – is relative by default.

For example, let’s take a look at how many people are in every house in Hogwarts.

To create the Pivot table, go to insert> Pivot table. Excel will automatically populate your Pivot table, but can always change the order of the data. Then you have four options to choose from.

Report filter

In this way you can only view certain lines in your data set.

To create a filter from House, for example, only choose students in Gryffindor.

Column and line names

These can be header or lines in the data set.

Note: Both line and column designations can contain data from your columns. For example, you can draw the first name either into the line or to the column designation, depending on how you want to see the data.

Value

In this section you can convert data to a number. Instead of just drawing a numerical value, you can sum, count, average, max, min, count counting numbers or carry out a few other manipulations with your data. If you pull a field to value, it counts by default.

The above example counts the number of students in every house. To recreate this Pivot table, go to the Pivot table and pull the house column both into the line names and the values. This summarizes the number of students connected to each house.

If functions

At the most basic level, you can use Excel to be used for a certain value whether a specified condition is true or false.

If the condition is true, you will receive a result. If the condition is wrong, you will receive a different result.

This popular tool is useful for comparisons and finding mistakes. However, if you are new to Excel, you may need a little more information to get the best out of this function.

Let’s take a look at the syntax of this function:

• = If (logical_test, value_if_true, (value_if_false))

• This could be with values: = If (a2> b2, “over budget”, “ok”)

In this example, you would like to find out where you spend too much. If your expenses (what in A2) is easy to see through expenses if your expenses (what in A2) is greater than your budget (what is in B2). You can then filter the data so that you can only see the advertising books in which you go through the budget.

The actual power of the IF function comes when you put together several IF instructions or put together “nest”. In this way you can set several conditions, achieve more specific results and organize your data in manageable pieces.

For example, areas are a way to segment your data for a better analysis. For example, you can categorize data in values ​​that are less than 10, 11 to 50 or 51 to 100.

= If (b3 <11, "10 or less", if (b3 <51, "11 to 50", if (b3 <100, "51 to 100"))))

Let’s talk about a few more if you have functions.

Countif function

The power of functions goes beyond simple true and false statements. With the Countif function, Excel can count the number of cells with which a word or number is displayed in any area of ​​cells.

Let us assume, for example, that you want to count the word “Gryffindor” in this data record.

Take a look at the syntax.

• The formula: = Countif (area, criteria)
• The formula with variables from the following example: = Countif (D: D, “Gryffindor”)

There are several variables in this formula:

reach

The area that the formula is supposed to cover.

In this adapting example, “D: D” shows that the first and last columns are both D. If you want to watch the columns C and D, use “C: D”.

Criteria

Whatever the number or a piece of text that you want to count for Excel.

Only use quotes if you want the result to be text instead of a number. In this example, “Gryffindor” is the only criterion.

To use this function, enter the Countif formula into each cell and press “Enter”. With the example above, this action shows how often the word “Gryffindor” is displayed in the data record.

Sumif function

Ready to make the IF function a little more complex? Suppose you want to analyze the number of leads that your blog generated by an author, not from the entire team.

With the Sumifs function you can add cells that meet certain criteria. You can add as many different criteria to the formula as you want.

Here is your formula:

• = Sumifs (sum_range, criteria_range1, criteria1, (criteria_range2, criteria 2) etc.)

These are many criteria. Let’s look at every part:

Sum_range

The area of ​​the cells that you will add.

Criteria_range1

The area that is searched for your first value.

Criteria1

This is the specific value that determines which cells are to be put together in criteria_range1.

note: Remember to use quotes when looking for text.

In the following example, the Sumif formula counts the total number of Gryffindor’s house points.

If and/or

The OR and and and and functions complete their if selection of function. These functions check several arguments. There is either true or wrongly back, depending on whether at least one of the arguments is true (this is or function) or whether everyone is true (this is and function).

Lost in a sea of ​​”and’s” and “or”? Don’t look at yourself yet. In practice or and never use functions alone. You must be nested in another, if in the function. Remember the syntax of a base -if function:

• = If (logical_test, value_if_true, (value_if_false))
• Now we fit into one or function in the logical_test or a function: = If (or (logical1, logical2), value_if_true, (value_if_false)))

To express it clearly, this combined formula enables a value to be returned if both conditions apply, in contrast to only one. With and/or functions, your formulas can be as simple or complex as you want as long as you understand the basics of the IF function.

Vlookup

Have you ever had two data records for two different spreadsheets that you want to combine into a single table?

Suppose you have a list of names and e -mail addresses in a table and a list of e -mail addresses and company names in another table. However, you want the names, E -Mail addresses and company names of these people to appear in a table.

Before using the formula, make sure that you have at least one column that appears identical in both places.

Note: Browse your data records to ensure that the data column you use to combine spreadsheets is exactly the same. This includes removing additional rooms.

In the following example, sheet 1 and sheet are two lists with different information about the same people. The common thread between the two are their e -mail addresses. Let us combine both data records so that all house information made of sheet two is translated to sheet one.

Enter the formula: = Vlookup (C2, sheet2! A: b, 2, wrong). This introduces all house data in sheet.

After you have seen how vlookup works, we check the formula.

• The formula: = Vlookup (search value, table array, column number, (area hookup))
• The formula with variables from the example: = Vlookup (C2, sheet2! A: b, 2, wrong)

There are several variables in this formula.

Search value

A value that searches in an array. So your Lookup value is the identical value that you have in both spreadsheets.

In the example, the search value is the first e -mail address in the list or cell 2 (C2).

Table array

Table arrays keep column -oriented or tabular data, such as the columns on sheet two, of which they pull their data.

This table array contains the data column that is identical to your search value in sheet 1, and the data column that you want to copy in sheet two.

In the example “A”, column A means two in sheet. The “B” means column B.

The table array is “Sheet2! A: B”.

Column number

Excel refers to columns as letters and lines as numbers. The column number is therefore the selected column for the new data you want to copy.

In the example, this would be the “house” column. “House” is column 2 in the table array.

Note: Your area can be more than two columns. For example, if there are three columns on sheet two – e -mail, age and house – and you also want to bring the house to the sheet, you can still use a vlookup. You only have to change the “2” to a “3” so that it withdraws the value in the third column. The formula for this would be: = Vlookup (C2: Blatt2! A: C, 3, wrong).)

reach

This term means that you are looking for a value within a value range. You can also use the term “false” to only achieve precisely accuracy.

Note: Vlookup is only withdraw the values ​​to the right of the column, which contains its identical data on the second sheet. For this reason, some people prefer to use the index instead and reach functions.

Index match

Like vlookup, the index and match functions draw data from a different data record in a central location. Here are the main differences:

Vlookup is a much simpler formula.

If you work with large data sets that need thousands of Lookups, the index match reduces the loading time in Excel.

Index match formulas work on the right of the left.

Vlookup formulas only act as to the right. So if you have to carry out a lookup with a column to the right of the gap of the results, you have to reorganize these columns to make a vlookup. This can be tedious with large data records and lead to mistakes.

Let’s take a look at an example. Let us assume that sheet one contains a list of names and your Hogwarts -E -Mail addresses. Sheet two contains a list of E -Mail addresses and the patronage of each student.

The information that lives in both leaves is the column of e -mail addresses. However, the column numbers for e -mail addresses vary on the two leaves. So you would use the index match formula instead of vlookup to avoid errors with column switches.

The index match formula is the formula of match nested in the index formula.

• The formula: = Index (table array, agreement of the formula)
• This will: = Index (table array, match (lookup_value, lookup_array))
• The formula with variables from the example: = Index (sheet2! A: A, (instead of1! C: c, shad2! C: c, 0)))

Here are the variables:

Table array

The area of ​​columns on sheet two, which contain the new data you want to bring on sheet.

In the example “A” means a column A and contains the “Patronus” information for everyone.

Search value

In this column, a column contains identical values ​​in both spreadsheets.

In the example, this is the “E -Mail” column on sheet 1, which is column C. Blatt1! C: C.

Lookup array

An array is also a group of values ​​in lines and columns that you want to search.

In this example, the Lookup array is the column in sheet two, which contains identical values ​​in both spreadsheets. So the “E -Mail” column on sheet two, Blatt2! C: C.

As soon as you have set your variables, enter the index match formula. Add it where the combined information should fill.

Data visualization

After learning formulas and functions, let us make your analysis visually. With a beautiful graphic, your audience can easily process your data and remember it.

Create a basic graphic.

First decide which type of graphic should be used. Bar diagrams and district diagrams will help you compare the categories. District diagrams compare part of a whole and are often best when one of the categories is much larger than the others. Bark diagrams emphasize incremental differences between the categories. Finally, ties diagrams can help display trends over time.

Next, mark the data you want to transform into a diagram. Then choose “diagrams” in the upper navigation. You can also use an insert> -idib for an older version of Excel. Then you can adapt and change your diagram until the instruction you hope to adapt.

Microsoft Excel can help your company grow.

Excel is a useful instrument for every small company. Regardless of whether you focus on marketing, HR, sales or service, these Microsoft Excel tips can increase your performance.

Regardless of whether you want to improve efficiency or productivity, Excel can help. You can find new trends and organize your data in usable findings. This can make your data analysis easier and make your daily tasks easier.

All it needs is a little know-how and some time with the software. So start learning and get ready to grow.

Note from the publisher: This post was originally published in April 2018 and updated for completeness.