How to Use Excel Like a Pro: 19 Easy Excel Tips, Tricks, & Shortcuts

sometimes, Excel seems besides good to be truthful. All I have to do is enter a rule, and pretty much anything I ‘d ever need to do manually can be done automatically .
necessitate to merge two sheets with similar data ? Excel can do it .
want to do simpleton mathematics ? Excel can do it .
motivation to combine information in multiple cells ? Excel can do it.

In this post, I ’ ll go over the best tips, tricks, and shortcuts you can use right now to take your Excel game to the adjacent level. No advanced Excel cognition required .
Download 10 Excel Templates for Marketers [Free Kit]

What is Excel?

Microsoft Excel is brawny data visual image and analysis software, which uses spreadsheets to store, organize, and track data sets with formulas and functions. Excel is used by marketers, accountants, data analysts, and other professionals. It ‘s part of the Microsoft Office suite of products. Alternatives include Google Sheets and Numbers .
Find more Excel alternatives here .

What is Excel used for?

Excel is used to store, analyze, and report on big amounts of data. It is much used by accounting teams for fiscal analysis, but can be used by any master to manage hanker and unwieldy datasets. Examples of Excel applications include balance sheets, budgets, or editorial calendars .

Excel is chiefly used for creating fiscal documents because of its strong computational powers. You ’ ll frequently find the software in account offices and teams because it allows accountants to automatically see sums, averages, and totals. With Excel, they can easily make sense of their business ’ data .
While Excel is chiefly known as an accounting tool, professionals in any field can use its features and formulas — particularly marketers — because it can be used for tracking any type of data. It removes the need to spend hours and hours counting cells or replicate and pasting performance numbers. Excel typically has a shortcut or agile fix that speeds up the work .
You can besides download Excel templates below for all of your marketing needs .

Featured Resource

Free Excel Templates

Fill out the form to download your free templates.

After you download the templates, it ’ s prison term to start using the software. Let ’ s cover the basics first .

Excel Basics

If you ‘re just starting out with Excel, there are a few basic commands that we suggest you become familiar with. These are things like :

  • Creating a new spreadsheet from scratch.
  • Executing basic computations like adding, subtracting, multiplying, and dividing.
  • Writing and formatting column text and titles.
  • Using Excel’s auto-fill features.
  • Adding or deleting single columns, rows, and spreadsheets. (Below, we’ll get into how to add things like multiple columns and rows.)
  • Keeping column and row titles visible as you scroll past them in a spreadsheet, so that you know what data you’re filling as you move further down the document.
  • Sorting your data in alphabetical order. 

Let ‘s explore a few of these more in-depth .
For case, why does auto-fill matter ?
If you have any basic Excel cognition, it ’ s likely you already know this agile flim-flam. But to cover our bases, allow me to show you the glory of autofill. This lets you promptly fill adjacent cells with several types of data, including values, series, and formulas .
There are multiple ways to deploy this sport, but the fill wield is among the easiest. Select the cells you want to be the generator, locate the fill up cover in the lower-right corner of the cellular telephone, and either drag the filling handle to cover cells you want to fill or barely double click :
excel autofill Similarly, sorting is an significant sport you ‘ll want to know when organizing your data in Excel .
sometimes you may have a list of data that has no organization any. possibly you exported a tilt of your marketing contacts or web log posts. Whatever the character may be, Excel ’ s classify feature will help you alphabetize any list .
Click on the datum in the column you want to sort. then click on the “ Data ” tab in your toolbar and look for the “ Sort ” option on the leave. If the “ A ” is on peak of the “ Z, ” you can just click on that release once. If the “ Z ” is on acme of the “ A, ” suction stop on the push button doubly. When the “ A ” is on top of the “ Z, ” that means your list will be sorted in alphabetic order. however, when the “ Z ” is on circus tent of the “ A, ” that means your list will be sorted in rearward alphabetic arrange .
Let ‘s explore more of the basics of Excel ( along with advanced features ) adjacent .

How to Use Excel

To use Excel, you alone need to input the datum into the rows and column. And then you ‘ll use formulas and functions to turn that data into insights .
We ‘re going to go over the best formula and functions you need to know. But first, let ‘s take a expect at the types of documents you can create using the software. That manner, you have an overarching understand of how you can use Excel in your daily .

Documents You Can Create in Excel

not sure how you can actually use Excel in your team ? hera is a number of documents you can create :

  • Income Statements: You can use an Excel spreadsheet to track a company’s sales activity and financial health.
  • Balance Sheets: Balance sheets are among the most common types of documents you can create with Excel. It allows you to get a holistic view of a company’s financial standing.
  • Calendar: You can easily create a spreadsheet monthly calendar to track events or other date-sensitive information.

here are some documents you can create specifically for marketers .
This is alone a minor sampling of the types of market and business documents you can create in Excel. We ’ ve created an extensive tilt of Excel templates you can use right now for marketing, invoice, undertaking management, budget, and more .
In the spirit of working more efficiently and avoiding long-winded, manual work, here are a few Excel formulas and functions you ’ ll need to know .

Excel Formulas

It ’ mho easy to get overwhelmed by the wide range of Excel formulas that you can use to make sense out of your data. If you ’ re fair getting started using Excel, you can rely on the follow formula to carry out some complex functions — without adding to the complexity of your learning path .

  • Equal sign: Before creating any formula, you’ll need to write an equal sign (=) in the cell where you want the result to appear.
  • Addition: To add the values of two or more cells, use the + sign. Example: =C5+D3.
  • Subtraction: To subtract the values of two or more cells, use the sign. Example: =C5-D3.
  • Multiplication: To multiply the values of two or more cells, use the * sign. Example: =C5*D3.
  • Division: To divide the values of two or more cells, use the / sign. Example: =C5/D3.

Putting all of these together, you can create a formula that adds, subtracts, multiplies, and divides all in one cell. example : = ( C5-D3 ) / ( ( A5+B6 ) *3 ) .
For more complex formula, you ’ ll need to use parentheses around the expressions to avoid by chance using the PEMDAS order of operations. Keep in judgment that you can use plain numbers in your formulas .

Excel Functions

Excel functions automate some of the tasks you would use in a distinctive rule. For exemplify, alternatively of using the + sign to add up a range of cells, you ’ d use the SUM function. Let ’ s look at a few more functions that will help automate calculations and tasks .

  • SUM: The SUM function automatically adds up a range of cells or numbers. To complete a sum, you would input the starting cell and the final cell with a colon in between. Here’s what that looks like: SUM(Cell1:Cell2). Example: =SUM(C5:C30).
  • AVERAGE: The AVERAGE function averages out the values of a range of cells. The syntax is the same as the SUM function: AVERAGE(Cell1:Cell2). Example: =AVERAGE(C5:C30).
  • IF: The IF function allows you to return values based on a logical test. The syntax is as follows: IF(logical_test, value_if_true, [value_if_false]). Example: =IF(A2>B2,”Over Budget”,”OK”).
  • VLOOKUP: The VLOOKUP function helps you search for anything on your sheet’s rows. The syntax is: VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE)). Example: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
  • INDEX: The INDEX function returns a value from within a range. The syntax is as follows: INDEX(array, row_num, [column_num]).
  • MATCH: The MATCH function looks for a certain item in a range of cells and returns the position of that item. It can be used in tandem with the INDEX function. The syntax is: MATCH(lookup_value, lookup_array, [match_type]).
  • COUNTIF: The COUNTIF function returns the number of cells that meet a certain criteria or have a certain value. The syntax is: COUNTIF(range, criteria). Example: =COUNTIF(A2:A5,”London”).

Okay, fix to get into the kernel ? Let ‘s get to it. ( And to all the Harry Potter fans out there … you ‘re welcome in advance. )

Excel Tips

  1. Use Pivot tables to recognize and make sense of data.
  2. Add more than one row or column.
  3. Use filters to simplify your data.
  4. Remove duplicate data points or sets.
  5. Transpose rows into columns.
  6. Split up text information between columns.
  7. Use these formulas for simple calculations.
  8. Get the average of numbers in your cells.
  9. Use conditional formatting to make cells automatically change color based on data.
  10. Use IF Excel formula to automate certain Excel functions.
  11. Use dollar signs to keep one cell’s formula the same regardless of where it moves.
  12. Use the VLOOKUP function to pull data from one area of a sheet to another.
  13. Use INDEX and MATCH formulas to pull data from horizontal columns.
  14. Use the COUNTIF function to make Excel count words or numbers in any range of cells.
  15. Combine cells using ampersand.
  16. Add checkboxes.
  17. Hyperlink a cell to a website.
  18. Add drop-down menus.
  19. Use the format painter.

Note : The GIFs and visuals are from a previous version of Excel. When applicable, the copy has been updated to provide instruction for users of both newer and older Excel versions .

1. Use Pivot tables to recognize and make sense of data.

Pivot tables are used to reorganize data in a spreadsheet. They wo n’t change the datum that you have, but they can sum up values and compare different data in your spreadsheet, depending on what you ‘d like them to do .
Let ‘s take a look at an model. Let ‘s say I want to take a expression at how many people are in each house at Hogwarts. You may be thinking that I do n’t have excessively much data, but for longer data sets, this will come in handy .
To create the Pivot Table, I go to Data > Pivot Table. If you ’ re using the most recent version of Excel, you ’ five hundred go to Insert > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. then, you have four options to choose from .

  • Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted to create a filter by house, I could choose to only include students in Gryffindor instead of all students.
  • Column Labels: These would be your headers in the dataset.
  • Row Labels: These could be your rows in the dataset. Both Row and Column labels can contain data from your columns (e.g. First Name can be dragged to either the Row or Column label — it just depends on how you want to see the data.)
  • Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default, when you drag a field to Value, it always does a count.

Since I want to count the number of students in each house, I ‘ll go to the Pivot postpone builder and drag the House column to both the Row Labels and the Values. This will sum up the issue of students associated with each family .
excel pivot table creation

2. Add more than one row or column.

As you play around with your data, you might find you ‘re constantly needing to add more rows and column. sometimes, you may even need to add hundreds of rows. Doing this one-by-one would be super long-winded. fortunately, there ‘s always an easier direction .
To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting rows or columns that you want to add. then, right-click and choose “ Insert. ”
In the exercise below, I want to add an extra three rows. By highlighting three rows and then clicking insert, I ‘m able to add an extra three lacuna rows into my spreadsheet quickly and well .
excel insert spaces

3. Use filters to simplify your data.

When you ‘re looking at very bombastic data sets, you do n’t normally need to be looking at every single rowing at the same time. sometimes, you only want to look at data that fit into certain criteria .
That ‘s where filters come in .
Filters allow you to pare down your data to only look at sealed rows at one time. In Excel, a percolate can be added to each column in your data — and from there, you can then choose which cells you want to view at once .
Let ‘s take a front at the example below. Add a percolate by clicking the Data pill and selecting “ Filter. ” Clicking the arrow next to the column headers and you ‘ll be able to choose whether you want your data to be organized in ascending or descending order, equally well as which particular rows you want to show .
In my Harry Potter example, let ‘s say I lone want to see the students in Gryffindor. By selecting the Gryffindor percolate, the other rows disappear .
excel filters Pro Tip : transcript and paste the values in the spreadsheet when a Filter is on to do extra analysis in another spreadsheet .

4. Remove duplicate data points or sets.

Larger data sets tend to have duplicate contentedness. You may have a list of multiple contacts in a company and only want to see the act of companies you have. In situations like this, removing the duplicates comes in quite handy .
To remove your duplicates, highlight the row or column that you want to remove duplicates of. then, go to the Data yellow journalism and select “ Remove Duplicates ” ( which is under the Tools subheader in the older version of Excel ). A pop-up book will appear to confirm which data you want to work with. Select “ Remove Duplicates, ” and you ‘re full to go .
excel duplicates removal
You can besides use this feature to remove an entire quarrel based on a duplicate column value. then if you have three rows with Harry Potter ‘s data and you entirely need to see one, then you can select the whole dataset and then remove duplicates based on e-mail. Your resulting list will have only singular names without any duplicates .

5. Transpose rows into columns.

When you have rows of data in your spreadsheet, you might decide you actually want to transform the items in one of those rows into column ( or frailty versa ). It would take a lot of time to copy and paste each individual header — but what the transfer feature of speech allows you to do is simply move your row data into column, or the other way around .
start by highlighting the column that you want to transpose into rows. Right-click it, and then select “ Copy. ” next, select the cells on your spreadsheet where you want your first base row or column to begin. Right-click on the cell, and then select “ Paste Special. ” A module will appear — at the penetrate, you ‘ll see an option to transpose. Check that box and choose OK. Your column will now be transferred to a course or vice-versa .
excel transpose
On newer versions of Excel, a drop-down will appear alternatively of a pop fly .
Excel transpose tool in newer versions

6. Split up text information between columns.

What if you want to split out information that ‘s in one cell into two different cells ? For case, possibly you want to pull out person ‘s company name through their e-mail cover. Or possibly you want to separate person ‘s full name into a first and concluding name for your e-mail marketing templates .
Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next, go to the Data pill and select “ Text to Columns. ” A faculty will appear with extra data .
first, you need to select either “ Delimited ” or “ Fixed Width. ”

  • “Delimited” means you want to break up the column based on characters such as commas, spaces, or tabs.
  • “Fixed Width” means you want to select the exact location on all the columns that you want the split to occur.

In the model character below, let ‘s choice “ Delimited ” so we can separate the full name into first name and last name .
then, it ‘s time to choose the Delimiters. This could be a yellow journalism, semi-colon, comma, distance, or something else. ( “ Something else ” could be the “ @ ” sign used in an e-mail address, for case. ) In our model, let ‘s choose the outer space. Excel will then show you a preview of what your newfangled columns will look like .
When you ‘re happy with the preview, iron “ adjacent. ” This page will allow you to select Advanced Formats if you choose to. When you ‘re done, click “ Finish. ”
excel text to column

7. Use formulas for simple calculations.

In addition to doing reasonably complex calculations, Excel can help you do simple arithmetic like adding, subtracting, multiplying, or dividing any of your data .

  • To add, use the + sign.
  • To subtract, use the – sign.
  • To multiply, use the * sign.
  • To divide, use the / sign.

You can besides use parentheses to ensure certain calculations are done first base. In the example below ( 10+10*10 ), the second and third 10 were multiplied together before adding the extra 10. however, if we made it ( 10+10 ) *10, the first and moment 10 would be added in concert foremost .
Excel simple formulas in action

8. Get the average of numbers in your cells.

If you want the average of a set of numbers, you can use the rule =AVERAGE(Cell1:Cell2). If you want to sum up a column of numbers, you can use the formula =SUM(Cell1:Cell2) .

9. Use conditional formatting to make cells automatically change color based on data.

conditional format allows you to change a cell ‘s color based on the information within the cell. For exercise, if you want to flag certain numbers that are above average or in the exceed 10 % of the data in your spreadsheet, you can do that. If you want to color code commonalities between different rows in Excel, you can do that. This will help you quickly see information that is important to you .
To get started, highlight the group of cells you want to use conditional format on. then, choose “ conditional format ” from the Home menu and select your logic from the dropdown. ( You can besides create your own rule if you want something unlike. ) A windowpane will pop up that prompts you to provide more information about your format govern. Select “ OK ” when you ‘re done, and you should see your results mechanically appear.

Excel conditional formatting

10. Use the IF Excel formula to automate certain Excel functions.

sometimes, we do n’t want to count the issue of times a value appears. alternatively, we want to input different information into a cell if there is a corresponding cell with that information .
For model, in the situation below, I want to award ten points to everyone who belongs in the Gryffindor house. alternatively of manually typing in 10 ‘s future to each Gryffindor student ‘s name, I can use the IF Excel formula to say that if the scholar is in Gryffindor, then they should get ten points .
The formula is : IF(logical_test, value_if_true, [value_if_false])
Example Shown Below : =IF(D2=”Gryffindor”,”10″,”0″)
In general terms, the recipe would be IF ( coherent Test, value of true, rate of false ). Let ‘s dig into each of these variables .

  • Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is D2=”Gryffindor” because we want to make sure that the cell corresponding with the student says “Gryffindor.” Make sure to put Gryffindor in quotation marks here.
  • Value_if_True: This is what we want the cell to show if the value is true. In this case, we want the cell to show “10” to indicate that the student was awarded the 10 points. Only use quotation marks if you want the result to be text instead of a number.
  • Value_if_False: This is what we want the cell to show if the value is false. In this case, for any student not in Gryffindor, we want the cell to show “0”. Only use quotation marks if you want the result to be text instead of a number.

Excel IF formula in action
Note : In the exemplar above, I awarded 10 points to everyone in Gryffindor. If I subsequently wanted to sum the total numeral of points, I would n’t be able to because the 10 ‘s are in quotes, frankincense making them text and not a total that Excel can sum .
The substantial exponent of the IF function comes when you string multiple IF statements together, or nest them. This allows you to set multiple conditions, get more specific results, and ultimately organize your data into more accomplishable chunks .
Ranges are one way to section your data for better analysis. For case, you can categorize data into values that are less than 10, 11 to 50, or 51 to 100. here ‘s how that looks in commit :
=IF(B3<11,“10 or less”,IF(B3<51,“11 to 50”,IF(B3<100,“51 to 100”)))
It can take some trial-and-error, but once you have the hang of it, IF rule will become your new Excel best acquaintance .

11. Use dollar signs to keep one cell’s formula the same regardless of where it moves.

Have you always seen a dollar sign in an Excel recipe ? When used in a recipe, it is n’t representing an american dollar ; alternatively, it makes sure that the demand column and row are held the same evening if you copy the lapp formula in adjacent rows .
You see, a cell reference point — when you refer to cell A5 from cell C5, for model — is relative by default. In that case, you ‘re actually referring to a cell that ‘s five column to the left field ( C minus A ) and in the same row ( 5 ). This is called a relative formula. When you copy a proportional formula from one cell to another, it ‘ll adjust the values in the convention based on where it ‘s moved. But sometimes, we want those values to stay the like no count whether they ‘re moved around or not — and we can do that by turning the recipe into an absolute formula .
To change the proportional recipe ( =A5+C5 ) into an absolute recipe, we ‘d precede the row and column values by dollar signs, like this : (=$A$5+$C$5). ( Learn more on Microsoft Office ‘s support foliate here. )

12. Use the VLOOKUP function to pull data from one area of a sheet to another.

Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet ?
For case, you might have a tilt of people ‘s names next to their e-mail addresses in one spreadsheet, and a list of those same people ‘s e-mail addresses following to their company identify in the other — but you want the names, electronic mail addresses, and ship’s company names of those people to appear in one place .
I have to combine data sets like this a lot — and when I do, the VLOOKUP is my go-to convention .
Before you use the formula, though, be absolutely indisputable that you have at least one column that appears identically in both places. Scour your data sets to make certain the column of data you ‘re using to combine your information is precisely the same, including no extra spaces .
The formula : =VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE))
The convention with variables from our model below : =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
In this rule, there are several variables. The watch is truthful when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1 .

  • Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).
  • Table Array: The table array is the range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.” “A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1.
  • Column Number: This tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that “House” is located in. “House” is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 — Email, Age, and House — and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the “2” to a “3” so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]
  • Approximate Match (TRUE) or Exact Match (FALSE): Use FALSE to ensure you pull in only exact value matches. If you use TRUE, the function will pull in approximate matches.

In the example below, Sheet 1 and Sheet 2 check lists describing unlike data about the same people, and the common thread between the two is their e-mail addresses. Let ‘s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1 .
Excel VLOOKUP function
thus when we type in the convention =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the house data into Sheet 1 .
Keep in judgment that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions alternatively .

13. Use INDEX and MATCH formulas to pull data from horizontal columns.

Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central localization. here are the chief differences :

  • VLOOKUP is a much simpler formula. If you’re working with large data sets that would require thousands of lookups, using the INDEX and MATCH function will significantly decrease load time in Excel.
  • The INDEX and MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you’d have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.

so if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 are n’t the lapp, then to do a VLOOKUP, I would need to switch around my column. In this case, I ‘d choose to do an INDEX and MATCH alternatively .
Let ‘s look at an exemplar. Let ‘s say Sheet 1 contains a list of people ‘s names and their Hogwarts email addresses, and Sheet 2 contains a number of people ‘s electronic mail addresses and the Patronus that each scholar has. ( For the non-Harry Potter fans out there, every enchantress or sorcerer has an animal defender called a “ Patronus ” associated with him or her. ) The information that lives in both sheets is the column containing electronic mail addresses, but this electronic mail address column is in different column numbers on each sheet. I ‘d use the INDEX and MATCH formulas rather of VLOOKUP so I would n’t have to switch any column about .
So what ‘s the rule, then ? The formula is actually the MATCH rule nested inside the INDEX formula. You ‘ll see I differentiated the MATCH formula using a different color here .
The rule : =INDEX(table array, MATCH formula)
This becomes : =INDEX(table array, MATCH (lookup_value, lookup_array))
The rule with variables from our example below : =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))
here are the variables :

  • Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. In our example, “A” means Column A, which contains the “Patronus” information for each person.
  • Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the “email” column on Sheet 1, which is Column C. So: Sheet1!C:C.
  • Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the “email” column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C.

once you have your variables straight, type in the INDEX and MATCH formulas in the top-most cell of the blank Patronus column on Sheet 1, where you want the combine information to live .
Excel INDEX and MATCH functions in action

14. Use the COUNTIF function to make Excel count words or numbers in any range of cells.

rather of manually counting how often a certain value or issue appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any image of cells .
For case, let ‘s say I want to count the number of times the parole “ Gryffindor ” appears in my data set .
The recipe : =COUNTIF(range, criteria)
The formula with variables from our exemplar below : =COUNTIF(D:D,”Gryffindor”)
In this rule, there are several variables :

  • Range: The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use “D:D” to indicate that the first and last column are both D. If I were looking at columns C and D, I would use “C:D.”
  • Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is “Gryffindor.”

Simply typing in the COUNTIF rule in any cell and pressing “ Enter ” will show me how many times the son “ Gryffindor ” appears in the dataset .
Excel COUNTIF function

15. Combine cells using &.

Databases tend to split out data to make it ampere exact as possible. For exemplar, alternatively of having a column that shows a person ‘s wax name, a database might have the data as a first name and then a last name in offprint column. Or, it may have a person ‘s placement separated by city, state, and nothing code. In Excel, you can combine cells with different data into one cell by using the “ & ” sign in your function .
The recipe with variables from our example below : =A2&” “&B2
Let ‘s go through the formula together using an exercise. Pretend we want to combine first names and last names into full names in a unmarried column. To do this, we ‘d beginning put our cursor in the blank cell where we want the full moon name to appear. following, we ‘d highlight one cell that contains a first name, type in an “ & ” bless, and then highlight a cell with the corresponding final name .
But you ‘re not finished — if all you type in is =A2 & B2, then there will not be a distance between the person ‘s first name and end name. To add that necessary space, use the function =A2&” “&B2. The quotation marks around the distance tell Excel to put a space in between the beginning and last name .
To make this truthful for multiple rows, plainly drag the corner of that inaugural cell downward as shown in the example .
Excel combination of cells

16. Add checkboxes.

If you ‘re using an Excel sheet to track customer data and want to oversee something that is n’t quantifiable, you could insert checkboxes into a column .
For example, if you ‘re using an Excel plane to manage your sales prospects and want to track whether you called them in the stopping point quarter, you could have a “ Called this quarter ? ” column and check off the cells in it when you ‘ve called the respective client .
hera ‘s how to do it .
Highlight a cell you ‘d like to add checkboxes to in your spreadsheet. then, snap DEVELOPER. then, under FORM CONTROLS, click the checkbox or the survival circle highlighted in the prototype below .
Excel checkboxes
once the box appears in the cellular telephone, copy it, highlight the cells you besides want it to appear in, and then paste it .

17. Hyperlink a cell to a website.

If you ‘re using your sheet to track sociable media or web site metrics, it can be helpful to have a mention column with the links each course is tracking. If you add a URL immediately into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you ‘re tracking, here ‘s how .
Highlight the words you want to hyperlink, then press Shift K. From there a corner will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or chink Enter .
If the identify shortcut is n’t working for any reason, you can besides do this manually by highlighting the cell and clicking Insert > Hyperlink .

18. Add drop-down menus.

sometimes, you ‘ll be using your spreadsheet to track processes or other qualitative things. rather than writing words into your sheet repetitively, such as “ Yes ”, “ No ”, “ Customer Stage ”, “ Sales leave ”, or “ Prospect ”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you ‘re tracking .
hera ‘s how to add drop-downs to your cells .
Highlight the cells you want the drop-downs to be in, then click the Data menu in the lead seafaring and press Validation .
Excel drop-down menu option
From there, you ‘ll see a Data Validation Settings box open. Look at the Allow options, then chatter Lists and blue-ribbon Drop-down list. Check the In-Cell dropdown clitoris, then weight-lift OK .

19. Use the format painter. 

As you ’ ve credibly noticed, Excel has a lot of features to make crunching numbers and analyzing your data immediate and easy. But if you ever spent some time formatting a sheet to your like, you know it can get a bit boring .
Don ’ t barren time repeating the like format commands over and over again. Use the format painter to easily copy the format from one area of the worksheet to another. To do then, choose the cell you ’ d like to replicate, then select the format cougar choice ( paintbrush icon ) from the top toolbar .

Excel Keyboard Shortcuts 

Creating reports in Excel is time-consuming adequate. How can we spend less fourth dimension navigate, format, and selecting items in our spreadsheet ? Glad you asked. There are a long ton of Excel shortcuts out there, including some of our favorites listed below .

Create a New Workbook

personal computer : Ctrl-N | Mac : Command-N

Select Entire Row

personal computer : Shift-Space | Mac : Shift-Space

Select Entire Column

personal computer : Ctrl-Space | Mac : Control-Space

Select Rest of Column

personal computer : Ctrl-Shift-Down/Up | Mac : Command-Shift-Down/Up

Select Rest of Row

personal computer : Ctrl-Shift-Right/Left | Mac : Command-Shift-Right/Left

Add Hyperlink

personal computer : Ctrl-K | Mac : Command-K

Open Format Cells Window

personal computer : Ctrl-1 | Mac : Command-1

Autosum Selected Cells

personal computer : Alt-= | Mac : Command-Shift-T

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

even if you ’ re not an accountant, you can silent use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you ’ ll be indisputable to use Excel to its fullest extent and get the most out of the software to grow your clientele .
Editor ‘s note : This post was in the first place published in August 2017 but has been updated for comprehensiveness.

excel marketing templates

Related Posts

Leave a Reply

Your email address will not be published.