Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty much anything I’d ever need to do manually can be done automatically.<\/p>\n
<\/a><\/p>\n Need to merge two sheets with similar data? Do simple math? Combine information in multiple cells? Excel can do it all.<\/p>\n In this post, I\u2019ll review the best tips, tricks, and shortcuts for taking your Excel game to the next level. The best part? You don\u2019t need advanced Excel knowledge<\/a>.<\/p>\n <\/a> <\/p>\n Microsoft Excel is powerful data visualization and analysis software. It uses spreadsheets to store, organize, and track data sets with formulas and functions.<\/p>\n Excel is used by marketers, accountants, data analysts, and other professionals. It’s part of the Microsoft Office suite of products. Excel alternatives<\/a> include Google Sheets<\/a> and Numbers.<\/p>\n <\/a> <\/p>\n Excel is primarily used to create financial documents because of its strong computational powers. You\u2019ll often find the software in accounting 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.<\/p>\n While Excel is primarily known as an accounting tool, professionals in any field can use its features and formulas \u2014 especially marketers \u2014 because it\u2019s valuable for tracking any type of data.<\/p>\n It removes the need to spend hours and hours counting cells or copying and pasting performance numbers. Excel typically has a shortcut or quick fix that speeds up the process.<\/p>\n There are several downloaded Excel templates you can use for your marketing needs. You can find some of them below.<\/p>\n While these templates will make using Excel less daunting, you still need to understand the basics.<\/p>\n Don\u2019t worry. I\u2019ve got you covered.<\/p>\n <\/a> <\/p>\n There are a million and one things you could do with Excel. However, everything starts with opening an Excel Sheet or Workbook.<\/p>\n You can open an Excel Sheet by creating a new one or clicking on an existing one.<\/p>\n\n If you don\u2019t have an existing worksheet, you can create one or choose from the many templates available in Excel.<\/p>\n\n A blank Excel Sheet can be intimidating. However, it gets easier once you familiarize yourself with how the navigation works and what each different menu means.<\/p>\n There are usually seven tabs \u2014 Home, Insert, Draw, Page Layout, Formulas, Data, Review, and View \u2014 all with different functions that help you analyze and present your data.<\/p>\n I say \u201cusually\u201d because you can add the Developer tab if needed.<\/p>\n To add the Developer tab, click the vertical ellipsis icon at the top of your Excel Sheet. Click More Commands. <\/strong>Switch to the Ribbon view. There, you can choose the tabs you want to appear on your Excel Sheet.<\/p>\n Click the checkbox next to Developer to add it. After closing the menu, the Develop tab should appear on your Excel Sheet.<\/p>\n It\u2019s okay if you don\u2019t know everything about these menus yet. You\u2019ll learn with practice.<\/p>\n As you\u2019re just starting, here are some basic commands I suggest you become familiar with:<\/p>\n Creating a new spreadsheet from scratch. We\u2019ll explore some of these functions in-depth later in the article.<\/p>\n To whet your appetite, let\u2019s consider the auto-fill feature<\/strong>.<\/p>\n You likely already know this quick trick if you have any basic Excel knowledge. But to cover our bases, allow me to show you the glory of autofill.<\/p>\n Autofill lets you quickly fill adjacent cells with several data types, including values, series, and formulas.<\/p>\n There are multiple ways to deploy this feature, but the fill handle is among the easiest. Select the cells you want to be the source, locate the fill handle in the lower-right corner of the cell, and either drag the fill handle to cover the cells you want to fill or just double click:<\/p>\n\n Similarly, sorting<\/strong> is an important feature you’ll want to know when organizing your data in Excel.<\/p>\n Sometimes, you may have a data list with no organization whatsoever. Maybe you exported a list of your marketing contacts or blog posts. Whatever the case, Excel\u2019s sort feature<\/a> will help you alphabetize any list.<\/p>\n Click on the data in the column you want to sort. Then click on the \u201cData\u201d tab in your toolbar and look for the \u201cSort\u201d option on the left.<\/p>\n If the \u201cA\u201d is on top of the \u201cZ,\u201d you can just click on that button once. If the \u201cZ\u201d is on top of the \u201cA,\u201d click on the button twice.<\/p>\n When the \u201cA\u201d is on top of the \u201cZ,\u201d your list will be sorted in alphabetical order. However, when the \u201cZ\u201d is on top of the \u201cA,\u201d your list will be sorted in reverse alphabetical order.<\/p>\n\n Image Source<\/a><\/em><\/p>\n Next, let’s explore more of the basics of Excel (along with advanced features).<\/p>\n <\/a> <\/p>\n To use Excel, you only need to input the data into the rows and columns. And then, you’ll use formulas and functions to turn that data into insights.<\/p>\n We\u2019ll go over the best formulas and functions you need to know. But first, let\u2019s look at the types of documents you can create using the software. That way, you have an overarching understanding of how to use Excel daily.<\/p>\n Not sure how you can actually use Excel in your team? Here is a list of documents you can create:<\/p>\n Income statements<\/a>. You can use an Excel spreadsheet to track a company\u2019s sales activity and financial health. <\/p>\n Balance sheets<\/a>. Balance sheets are among the most common documents you can create with Excel. It gives you a holistic view of a company\u2019s financial standing. <\/p>\n Calendar<\/a>. You can easily create a spreadsheet monthly calendar to track events or other date-sensitive information. <\/p>\n Here are some documents you can create specifically for marketers.<\/p>\n Marketing budgets<\/a>. Excel is a strong budget-keeping tool. You can create and track marketing budgets and spend using Excel. <\/p>\n Pro tip:<\/strong> If you don\u2019t want to create a document from scratch, download our marketing budget templates<\/a> for free.<\/p>\n Marketing reports<\/a>. If you don\u2019t use a marketing tool like HubSpot\u2019s Marketing Hub<\/a>, you might need a dashboard with all your reports. Excel is an excellent tool for creating marketing reports. <\/p>\n Pro tip:<\/strong> Download free Excel marketing reporting templates<\/a> here.<\/p>\n Editorial calendars<\/a>. You can create editorial calendars in Excel. The tab format makes tracking your content creation efforts for custom time ranges extremely easy. <\/p>\n Pro tip: <\/strong>Download a free editorial content calendar<\/a> template here.<\/p>\n Traffic and leads calculator<\/a>. Because of its strong computational powers, Excel is an excellent tool for creating all sorts of calculators \u2014 including one for tracking leads and traffic. <\/p>\n Pro tip:<\/strong> Grab a free pre-made lead goal calculator<\/a> to get a jump start.<\/p>\n The above is only a tiny sampling of the marketing and business documents you can create in Excel. We\u2019ve created an extensive list of Excel templates<\/a> you can use right now for marketing, invoicing, project management, budgeting, and more.<\/p>\n\n In the spirit of working more efficiently and avoiding tedious, manual work, here are a few Excel formulas and functions you\u2019ll need to know.<\/p>\n It\u2019s easy to get overwhelmed by the wide range of Excel formulas<\/a> you can use to make sense of your data. If you\u2019re just getting started using Excel, you can rely on the following formulas to carry out some complex functions without adding to the complexity of your learning path.<\/p>\n Equal sign. <\/strong>Before creating any formula, you\u2019ll need to write an equal sign (=) in the cell where you want the result to appear. <\/p>\n Addition.<\/strong> To add the values of two or more cells, use the + sign. Example: =C5+D3.<\/em><\/p>\n Subtraction. <\/strong>To subtract the values of two or more cells, use the – sign. Example: =C5-D3.<\/em><\/p>\n Multiplication.<\/strong> To multiply the values of two or more cells, use the * sign. Example: =C5*D3.<\/em><\/p>\n Division.<\/strong> To divide the values of two or more cells, use the \/ sign. Example: =C5\/D3.<\/em><\/p>\n Here\u2019s how the results of these formulas might look:<\/p>\n\n Putting all these together, you can create a formula that adds, subtracts, multiplies, and divides all in one cell. Example: =(C5-D3)\/((A5+B6)*3)<\/em>.<\/p>\n For more complex formulas, you\u2019ll need to use parentheses around the expressions to follow the PEMDAS order of operations<\/a>. Keep in mind that you can use plain numbers in your formulas.<\/p>\n Excel functions automate some of the tasks you would use in a typical formula. For instance, instead of using the + sign to add up a range of cells, you\u2019d use the SUM function. Let\u2019s look at a few more functions to help automate calculations and tasks.<\/p>\n SUM<\/a>. The SUM function automatically adds up a range of cells or numbers. To complete a sum, you would input the starting and final cells with a colon in between. Here\u2019s what that looks like: SUM(Cell1:Cell2)<\/strong>. Example: =SUM(C5:C30).<\/em><\/p>\n AVERAGE<\/a>. The AVERAGE function averages out the values of a range of cells. The syntax is the same as the SUM function: AVERAGE(Cell1:Cell2)<\/strong>. Example: =AVERAGE(C5:C30).<\/em><\/p>\n IF<\/a>. 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])<\/strong>. Example: =IF(A2>B2,\u201cOver Budget\u201d,\u201cOK\u201d).<\/em><\/p>\n VLOOKUP<\/a>. The VLOOKUP function helps you search for anything on your sheet\u2019s rows. The syntax is: VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE))<\/strong>. Example: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).<\/em><\/p>\n INDEX<\/a>. The INDEX function returns a value from within a range. The syntax is INDEX(array, row_num, [column_num])<\/strong>. <\/p>\n MATCH<\/a>. 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])<\/strong>. <\/p>\n COUNTIF<\/a>. The COUNTIF function returns the number of cells that meet certain criteria or have a certain value. The syntax is COUNTIF(range, criteria)<\/strong>. Example: =COUNTIF(A2:A5,\u201cLondon\u201d).<\/em><\/p>\n Okay, ready to get into the nitty-gritty? Let\u2019s get to it. (And to all the Harry Potter fans out there … you\u2019re welcome in advance.)<\/p>\n <\/a> <\/p>\n Note:<\/strong> Some of the GIFs and visuals are from a previous version of Excel. When applicable, the copy has been updated to provide instructions for users of both newer and older Excel versions.<\/p>\n Pivot tables are used to reorganize data in a spreadsheet. They won\u2019t change the data you have, but they can sum up values and compare different information in your spreadsheet, depending on what you\u2019d like them to do.<\/p>\n Let\u2018s consider an example. Let\u2019s say I want to look at the number of people in each house at Hogwarts.<\/p>\n To create the Pivot Table, I go to Data > Pivot Table<\/strong>. If you\u2019re using the most recent version of Excel, you\u2019d go to Insert > Pivot Table.<\/strong> Excel will automatically populate your Pivot Table, but you can always change the order of the data. Then, you have four options to choose from.<\/p>\n Report Filter.<\/strong> This allows you to look at specific rows in your dataset. For example, if I wanted to create a filter by house, I could choose to include only students in Gryffindor instead of all students. <\/p>\n Column Labels.<\/strong> These would be your headers in the dataset. <\/p>\n Row Labels.<\/strong> These could be your rows in the dataset. Both Row and Column labels can contain data from your columns (e.g., You can drag First Name to either the Row or Column label \u2014 it just depends on how you want to see the data.) <\/p>\n Value.<\/strong> 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. <\/p>\n Since I want to count the number of students in each house, I’ll go to the Pivot table builder and drag the House column to the Row Labels and the Values. This will sum up the number of students associated with each house.<\/p>\n\n As you play around with your data, you might find you constantly need to add more rows and columns. Sometimes, you may need to add hundreds of rows. Doing this one by one would be super tedious. Luckily, there\u2019s always an easier way.<\/p>\n To add multiple rows or columns in a spreadsheet, highlight the number of preexisting rows or columns you want to add. Then, right-click and select \u201cInsert.\u201d<\/p>\n In the example below, I want to add three rows. By highlighting three rows and then clicking insert, I’m able to add three blank rows to my spreadsheet quickly and easily.<\/p>\n\n When examining huge data sets, you\u2019re sometimes only interested in data from rows that fit specific criteria.<\/p>\n That’s where filters come in.<\/p>\n Filters allow you to pare down your data to look at only specific rows at one time. Excel allows you to add a filter to each column in your data, and from there, you can choose which cells you want to view at once.<\/p>\n Let\u2019s take a look at the example below. Add a filter by clicking the Data tab and selecting \u201cFilter.\u201d Clicking the arrow next to the column headers, you\u2019ll be able to choose whether you want your data to be organized in ascending or descending order, as well as which specific rows you want to show.<\/p>\n In my Harry Potter example, let’s say I only want to see the students in Gryffindor. By selecting the Gryffindor filter, the other rows disappear.<\/p>\n\n Pro tip:<\/strong> Copy and paste the values in the spreadsheet when a Filter is on to do additional analysis in another spreadsheet.<\/p>\n Larger data sets tend to have duplicate content. For example, you may have a list of multiple contacts in a company and only want to see the number of companies you have. In situations like this, removing the duplicates comes in quite handy.<\/p>\n To remove your duplicates, highlight the row or column you want to remove duplicates of. Then, go to the Data tab and select \u201cRemove Duplicates\u201d (which is under the Tools subheader in the older version of Excel).<\/p>\n A pop-up will appear to confirm which data you want to work with. Select \u201cRemove Duplicates,\u201d and you’re good to go.<\/p>\n\n You can also use this feature to remove an entire row based on a duplicate column value. So if you have three rows with Harry Potter’s information and only need to see one, then you can select the whole dataset and remove duplicates based on email. Your resulting list will have unique names without any duplicates.<\/p>\n When you have rows of data in your spreadsheet, you may want to transform the items in one of those rows into columns (or vice versa). It would take a lot of time to copy and paste each individual header. The transpose feature allows you to move your row data into columns or vice versa.<\/p>\n Start by highlighting the column that you want to transpose into rows. Right-click it, and then select \u201cCopy.\u201d Next, select the cells on your spreadsheet where you want your first row or column to begin. Right-click on the cell, and then select \u201cPaste Special.\u201d<\/p>\n A module will appear \u2014 at the bottom, you’ll see an option to transpose. Check that box and select OK. Your column will now be transferred to a row or vice-versa.<\/p>\n\n Note<\/strong>: On newer versions of Excel, a drop-down will appear instead of a pop-up.<\/p>\n What if you want to split information in one cell into two different cells?<\/p>\n For example, maybe you want to pull someone\u2019s company name through their email address. Or perhaps you want to separate someone’s full name into a first and last name for your email marketing templates.<\/p>\n Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next, go to the Data tab and select \u201cText to Columns.\u201d A module will appear with additional information.<\/p>\n First, you need to select either \u201cDelimited\u201d or \u201cFixed Width.\u201d<\/p>\n \u201cDelimited\u201d means you want to break up the column based on characters such as commas, spaces, or tabs. In the example case below, let’s select \u201cDelimited\u201d to separate the full name into first and last names.<\/p>\n Then, it\u2019s time to choose the Delimiters. This could be a tab, semi-colon, comma, space, or something else. (\u201cSomething else\u201d could be the \u201c@\u201d sign used in an email address, for example.)<\/p>\n In our example, let\u2019s choose the space. Excel will then show you a preview of what your new columns will look like.<\/p>\n When you\u2019re happy with the preview, press \u201cNext.\u201d This page will allow you to select Advanced Formats if you choose to. When you\u2019re done, click \u201cFinish.\u201d<\/p>\n\n In addition to doing pretty complex calculations, Excel can help you perform simple arithmetic, such as adding, subtracting, multiplying, or dividing any of your data.<\/p>\n To add, use the + sign. You can also use parentheses to ensure Excel performs specific calculations first. In the example below (10+10*10), the second and third 10 were multiplied together before adding the additional 10. However, if we made it (10+10)*10, the first and second 10 would be added together first.<\/p>\n\n If you want the average of a set of numbers, you can use the formula =AVERAGE(Cell1:Cell2). If you want to sum up a column of numbers, use the formula =SUM(Cell1:Cell2).<\/p>\n Conditional formatting allows you to change a cell’s color based on the information within the cell.<\/p>\n For example, if you want to flag specific numbers above average or in the top 10% of the data in your spreadsheet, color code commonalities between different rows in Excel, or something else, you can do that.<\/p>\n This will help you quickly see information that is important to you.<\/p>\n To get started, highlight the group of cells you want to use conditional formatting on. Then, choose \u201cConditional Formatting\u201d from the Home menu and select your logic from the dropdown. (You can also create your own rule if you want something different.)<\/p>\n A window will pop up that prompts you to provide more information about your formatting rule. Select \u201cOK\u201d when you’re done, and you should see your results automatically appear.<\/p>\n\n Sometimes, we don’t want to count the number of times a value appears. Instead, we want to input different information into a cell if there is a corresponding cell with that information.<\/p>\n For example, in the situation below, I want to award ten points to everyone who belongs in the Gryffindor house. Instead of manually typing in 10\u2018s next to each Gryffindor student\u2019s name, I can use the IF Excel formula to say that if the student is in Gryffindor, then they should get ten points.<\/p>\n The formula is: IF(logical_test, value_if_true, [value_if_false])<\/strong><\/p>\n Example Shown Below: =IF(D2=\u201cGryffindor\u201d,\u201c10\u201d,\u201c0\u201d)<\/strong><\/p>\n In general terms, the formula would be IF(Logical Test, value of true, value of false). Let’s dig into each of these variables.<\/p>\n Logical_Test.<\/strong> The logical test is the \u201cIF\u201d part of the statement. In this case, the logic is D2=\u201cGryffindor\u201d because we want to make sure that the cell corresponding with the student says \u201cGryffindor.\u201d Make sure to put Gryffindor in quotation marks here. <\/p>\n Value_if_True.<\/strong> This is what we want the cell to show if the value is true. In this case, we want the cell to show \u201c10\u201d to indicate that the student was awarded the 10 points. <\/p>\n Value_if_False. <\/strong>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 \u201c0\u201d. <\/p>\n Note:<\/strong> Only use quotation marks if you want the result to be text instead of a number.<\/p>\n\n Note:<\/strong> In the example above, I awarded 10 points to everyone in Gryffindor. If I later wanted to sum the total number of points, I wouldn\u2019t be able to because the 10\u2019s are in quotes, thus making them text and not a number that Excel can sum.<\/p>\n The real power 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 manageable chunks.<\/p>\n Ranges are one way to segment your data for better analysis. For example, you can categorize data into values less than 10, 11 to 50, or 51 to 100. Here’s how that looks in practice:<\/p>\n =IF(B3<11,\u201c10 or less\u201d,IF(B3<51,\u201c11 to 50\u201d,IF(B3<100,\u201c51 to 100\u201d)))<\/strong><\/p>\n It can take some trial and error, but once you have the hang of it, IF formulas will become your new Excel best friend.<\/p>\n Have you ever seen a dollar sign in an Excel formula? When used in a formula, it doesn’t represent an American dollar; instead, it ensures that the exact column and row are held the same even if you copy the same formula in adjacent rows.<\/p>\n You see, a cell reference \u2014 when you refer to cell A5 from cell C5, for example \u2014 is relative by default. In that case, you\u2019re actually referring to a cell that\u2019s five columns to the left (C minus A) and in the same row (5).<\/p>\n This is called a relative formula. When you copy a relative formula from one cell to another, it\u2019ll adjust the values in the formula based on where it\u2019s moved.<\/p>\n But sometimes, we want those values to stay the same no matter whether they’re moved around or not \u2014 and we can do that by turning the formula into an absolute formula.<\/p>\n To change the relative formula (=A5+C5) into an absolute formula, we’d precede the row and column values by dollar signs like this: (=$A$5+$C$5)<\/strong>. (Learn more on Microsoft Office’s support page here<\/a>.)<\/p>\n Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?<\/p>\n For example, you might have a list of people\u2019s names next to their email addresses in one spreadsheet and a list of those same people\u2019s email addresses next to their company names in the other \u2014 but you want the names, email addresses, and company names of those people to appear in one place.<\/p>\n I have to combine data sets like this a lot \u2014 and when I do, the VLOOKUP is my go-to formula.<\/p>\n Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to ensure the column of data you’re using to combine your information is the same, including no extra spaces.<\/p>\n The formula: =VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE))<\/strong><\/p>\n The formula with variables from our example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)<\/strong><\/p>\n In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 into Sheet 1.<\/p>\n Lookup Value.<\/strong> This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the following example, this means the first email address on the list or cell 2 (C2). <\/p>\n Table Array.<\/strong> The table array is the range of columns on Sheet 2 you\u2018re 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\u2019re trying to copy to Sheet 1. In our example, this is \u201cSheet2!A:B.\u201d \u201cA\u201d 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 \u201cB\u201d means Column B, which contains the information only available in Sheet 2 that you want to translate to Sheet 1. <\/p>\n Column Number.<\/strong> 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 \u201cHouse\u201d is located in. \u201cHouse\u201d 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 \u2014 Email, Age, and House \u2014 and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the \u201c2\u201d to a \u201c3\u201d so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).] <\/p>\n Approximate Match (TRUE) or Exact Match (FALSE).<\/strong> Use FALSE to ensure you pull in only exact value matches. If you use TRUE, the function will pull in approximate matches. <\/p>\n In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let’s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.<\/p>\n\n So when we type in the formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the house data into Sheet 1.<\/p>\n Remember that VLOOKUP will only pull back values from the second sheet 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 instead.<\/p>\n Like VLOOKUP, the INDEX and MATCH functions pull data from another dataset into one central location. Here are the main differences:<\/p>\n VLOOKUP is a much simpler formula. If you’re working with large data sets requiring thousands of lookups, using the INDEX and MATCH functions will significantly decrease load time in Excel. 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 aren\u2018t the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I\u2019d choose to do an INDEX and MATCH instead.<\/p>\n Let\u2019s look at an example. Let\u2019s say Sheet 1 contains a list of people\u2019s names and their Hogwarts email addresses, and Sheet 2 contains a list of people\u2019s email addresses and each student’s Patronus. (For non-Harry Potter fans, every witch or wizard has an animal guardian called a \u201cPatronus\u201d associated with them.)<\/p>\n The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I\u2018d use the INDEX and MATCH formulas instead of VLOOKUP so I wouldn\u2019t have to switch any columns around.<\/p>\n So what\u2019s the formula, then? The formula is actually the MATCH formula nested inside the INDEX formula. You\u2019ll see I differentiated the MATCH formula using a different color here.<\/p>\n The formula: =INDEX(table array, MATCH formula)<\/strong><\/p>\n This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))<\/strong><\/p>\n The formula with variables from our example below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))<\/strong><\/p>\n Here are the variables:<\/p>\n Table Array.<\/strong> The range of columns on Sheet 2 containing the new data you want to bring to Sheet 1. In our example, \u201cA\u201d means Column A, which contains the \u201cPatronus\u201d information for each person. <\/p>\n Lookup Value. <\/strong>This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the \u201cemail\u201d column on Sheet 1, which is Column C. So: Sheet1!C:C. <\/p>\n Lookup Array.<\/strong> This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the \u201cemail\u201d column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C. <\/p>\n 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 combined information to live.<\/p>\n\n Instead of manually counting how often a specific value or number 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 range of cells.<\/p>\n For example, let’s say I want to count the number of times the word \u201cGryffindor\u201d appears in my data set.<\/p>\n The formula: =COUNTIF(range, criteria)<\/strong><\/p>\n The formula with variables from our example below: =COUNTIF(D:D,\u201cGryffindor\u201d)<\/strong><\/p>\n In this formula, there are several variables:<\/p>\n Range.<\/strong> The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use \u201cD:D\u201d to indicate that the first and last columns are both D. If I were looking at columns C and D, I would use \u201cC:D.\u201d <\/p>\n Criteria.<\/strong> 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 \u201cGryffindor.\u201d <\/p>\n Simply typing in the COUNTIF formula in any cell and pressing \u201cEnter\u201d will show me how many times the word \u201cGryffindor\u201d appears in the dataset.<\/p>\n\n Databases tend to split out data to make it as exact as possible.<\/p>\n For example, instead of having a column that shows a person\u2018s full name, a database might have the data as a first name and then a last name in separate columns.<\/p>\n Or, it may have a person\u2019s location separated by city, state, and zip code. In Excel, you can combine cells with different data into one cell using the \u201c&\u201d sign in your function.<\/p>\n The formula with variables from our example below: =A2&\u201c \u201d&B2<\/strong><\/p>\n Let\u2018s go through the formula together using an example. Pretend we want to combine first and last names into full names in a single column.<\/p>\n To do this, we\u2019d first put our cursor in the blank cell where we want the full name to appear. Next, we’d highlight one cell that contains a first name, type in an \u201c&\u201d sign, and then highlight a cell with the corresponding last name.<\/p>\n But you\u2018re not finished \u2014 if all you type in is =A2&B2, there will not be a space between the person\u2019s first and last names. To add that necessary space, use the function =A2&\u201c \u201d&B2<\/strong>. The quotation marks around the space tell Excel to put a space between the first and last names.<\/p>\n To make this true for multiple rows, drag the corner of that first cell downward, as shown in the example.<\/p>\n\n If you\u2019re using an Excel sheet to track customer data and want to oversee something that isn\u2019t quantifiable, you could insert checkboxes into a column.<\/p>\n For example, if you\u2019re using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a \u201cCalled this quarter?\u201d column and check off the cells in it when you\u2019ve called the respective client.<\/p>\n Here’s how to do it.<\/p>\n Highlight a cell to which you’d like to add checkboxes to your spreadsheet. Then, click Developer and then Checkbox.<\/p>\n\n Drag or copy the checkbox to the cells where you want them to appear.<\/p>\n If you\u2018re using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking.<\/p>\n If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words like a page title or the headline of a post you\u2019re tracking, here’s how.<\/p>\n Highlight the words you want to hyperlink, then press Shift K. A box will pop up, allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.<\/p>\n If the key shortcut isn’t working for any reason, you can also do this manually. Highlight the cell, right-click, and choose Hyperlink<\/strong> from the drop-down menu.<\/p>\n Sometimes, you\u2019ll use your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as \u201cYes,\u201d \u201cNo,\u201d \u201cCustomer Stage,\u201d \u201cSales Lead,\u201d or \u201cProspect,\u201d you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you\u2019re tracking.<\/p>\n Here’s how to add drop-downs to your cells.<\/p>\n Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.<\/p>\n\n From there, a Data Validation Settings box will open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.<\/p>\n As you\u2019ve probably noticed, Excel has many features to make crunching numbers and analyzing your data quick and easy. But if you’ve ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.<\/p>\n Don\u2019t waste time repeating the same formatting commands over and over again.<\/p>\n Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you\u2019d like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.<\/p>\n\n Converting your data into a table makes it visually appealing and provides improved data management and analysis capabilities.<\/p>\n To get started, you\u2019ll need to select the range of cells that you want to convert into a table. Then, go to the Home tab in the Excel ribbon. In the Styles group, click the Format as Table<\/strong> button \u2014 it looks like a grid of cells. Then, choose a table style from the available options or customize a table if desired.<\/p>\n\n In the Create Table dialog box, make sure the range you selected is correct. If Excel does not automatically detect the range correctly, you can adjust it manually.<\/p>\n If your table has headers (column names), ensure that the \u201cMy table has headers\u201d option is checked. This allows Excel to treat the first row as the header row.<\/p>\n Once everything is ready, click the OK button, and Excel will convert your selected data into a table.<\/p>\n\n After your data is converted into a table, you’ll notice some additional features and functionalities become available:<\/p>\n The table is automatically assigned a name, such as \u201cTable1\u201d or \u201cTable2,\u201d which you can modify if needed. In addition to making your data more organized, tables can help you conduct what-if analyses. This allows you to test various combinations of input values and observe the resulting outcomes.<\/p>\n What-if analysis can be beneficial in decision-making, planning, forecasting, financial modeling, sensitivity analysis, resource planning, and more.<\/p>\n To get started, you\u2019ll need to set up your worksheet with the necessary formulas and variables you want to analyze. Then, determine the input values that you want to vary. Typically, you will choose one or two input variables.<\/p>\n Select the cell where you want to display the results of your what-if analysis. Then, click the What-If Analysis button in the Data tab. From the dropdown menu, select Data Table.<\/p>\n In the Table Input dialog box, enter the input values that you want to test for each variable. If you have one variable, enter the different input values in a column or row. If you have two variables, enter the combinations in a table format.<\/p>\n Select the cells in the table area corresponding to the formula cell you want to analyze. This is the cell that will display the results for each combination of input values.<\/p>\n Click OK to generate the data table. Excel will calculate the formula for each combination of input values and display the results in the selected cells. The data table acts as a grid, showing the various scenarios and their corresponding outcomes.<\/p>\n Once your table is created, you can use it to identify trends, patterns, or specific values of interest. Play around with the input values and see how it may affect the final results.<\/p>\n Instead of referring to a range of cells by its coordinates (e.g., A1:B10), you can assign a name to it. This makes formulas more readable and easier to manage.<\/p>\n To get started, select the cell or range you want to name. Go to the Formulas tab in the Excel ribbon and click on the Define Name button in the Defined Names group.<\/p>\n In the New Name dialog box, enter a name for the selected cell or range in the Name field. Make sure the name is descriptive and easy to remember.<\/p>\n By default, Excel assigns the selected cell or range’s reference to the Refers to field in the dialog box. If needed, you can modify the reference to include additional cells or adjust the range.<\/p>\n Click the OK button to save the named range. Once you’ve named a range, you can use it in your formulas by simply typing the name instead of the cell reference. For example, if you named cell A1 as \u201cRevenue,\u201d you could use =Revenue instead of =A1 in your formulas.<\/p>\n Using named ranges offers several benefits:<\/p>\n Improved formula readability.<\/strong> Named ranges make formulas more straightforward to understand and navigate, especially in complex calculations or large datasets. <\/p>\n Flexibility for range adjustments.<\/strong> If your dataset changes, you can easily modify the range assigned to a named range without updating each formula that references it. <\/p>\n Enhanced collaboration.<\/strong> Named ranges make it easier to collaborate with others, as they can understand the purpose of a named range and use it in their own calculations. <\/p>\n Simplified data analysis.<\/strong> When using named ranges, you can create more intuitive data analysis by referring to named ranges in functions like SUM, AVERAGE, COUNTIF, etc. <\/p>\n To manage named ranges, go to the Formulas tab and click on the Name Manager button in the Defined Names group. The Name Manager offers functionalities to modify, delete, or review existing named ranges.<\/p>\n Grouping data in Excel allows you to organize, analyze, and present information more effectively, making it easier to identify patterns, trends, and insights within your data. For instance, if you have a list of leads generated, you can group the data by month to create a monthly performance report.<\/p>\n Grouping data especially makes it easier to navigate and work with large data sets. It helps in organization and reduces clutter by collapsing the groups that are not immediately needed.<\/p>\n To group data in Excel, select the range of cells or columns that you want to group. Make sure the data is sorted properly if needed.<\/p>\n On the Data tab in the Excel ribbon, click on the Group button. It is usually found in the Outline or Data Tools group.<\/p>\n\n You can specify the grouping levels by choosing options like Rows or Columns. For example, you can select Months if you want to group data by month.<\/p>\n You can also set additional options, such as Summary rows below details, or collapse the outline to the summary levels. These options affect how the grouped data is displayed.<\/p>\n Once you have the options you want selected, click on the OK button, and Excel will group the selected data based on your settings.<\/p>\n After your data is grouped, you will see a plus (+) or minus (-) button next to the grouped rows or columns. Clicking on the plus button expands the group to show the individual records, and clicking on the minus button collapses the group to hide the details.<\/p>\n Why format and clean up your spreadsheet manually when you can do it in just a few clicks? Using the Find & Select tool can help you maintain document accuracy and consistency.<\/p>\n To get started, open the Excel worksheet that contains the data you want to search. Press the Ctrl + F keys on your keyboard or go to the Home tab and click on the Find & Select drop-down menu. Then, select Find from the menu. The Find and Replace dialog box will open.<\/p>\n\n In the Find field, enter the specific data you want to find. Optionally, you can narrow your search to particular cells, rows, columns, or formulas by choosing the appropriate options in the dialog box.<\/p>\n Click on the Find Next button to search for the first occurrence of the data. Excel will highlight the cell containing the data.<\/p>\n To replace the found data with new information, click the Replace button in the dialog box. This will replace the highlighted occurrence with the data you enter in the Replace field.<\/p>\n To replace all occurrences of the data at once, click on the Replace All button. You can close the dialog box once you have finished finding and replacing what you want.<\/p>\n Note<\/strong>: Be cautious when using the Replace All feature, as it replaces all occurrences without confirmation. It is always a good practice to review each replacement carefully before using the Replace All option.<\/p>\n Protecting your work in Excel is essential for data security, maintaining data integrity, preserving intellectual property, and complying with legal or regulatory requirements. It allows you to control who can access and modify your work, minimizing risks and maintaining the quality and confidentiality of your data.<\/p>\n Here are a couple of ways you can protect your work:<\/p>\n Open your Excel worksheet and navigate to the Review<\/strong> tab. This follows a process similar to protecting a worksheet. The Protect Workbook <\/strong>selection is next to the Protect Worksheet <\/strong>selection.<\/p>\n\n After clicking Protect Workbook, <\/strong>choose your password.<\/p>\n\n Taking these extra steps ensures your work is protected. Just make sure to keep your passwords safe and secure.<\/p>\n To display data in unique ways, use custom number formats. Doing this can help with data presentation, data clarity, consistency, localization, and masking of sensitive data.<\/p>\n To get started, select the cell or range of cells you want to format. Then, click the menu above the percentage sign under the Home tab.<\/p>\n\n Select More Number Formats <\/strong>from the dropdown. Select Custom.<\/p>\n In the Type field, you can enter a custom number format code to define your desired format. Here are some examples of custom number formats:<\/p>\n To display numbers with a specific number of decimal places, use the 0<\/strong> or # <\/strong>symbol to represent a digit and a zero or hashtag without a decimal point to represent optional digits. For example, 0.00<\/strong> will display two decimal places, 0.###<\/strong> will display up to three decimal places, and ###<\/strong> will display no decimal places. As you enter your custom number format in the Type field, you will see a Sample section showing how the format will be applied. Click OK to apply the custom number format to the selected cells.<\/p>\n Although the Excel ribbon already contains various tools for executing common functions and commands, you can customize it to fit your specific needs and preferences.<\/p>\n This can help streamline your workflow and make commonly used commands more easily accessible. It also allows you to remove unnecessary elements that you don\u2019t use, making it easier to navigate and find the tools you need.<\/p>\n\n To make customizations, start by right-clicking on an empty ribbon area and selecting Customize the Ribbon. In the Excel Options window that appears, you’ll see two sections. The left section displays the tabs currently visible in the ribbon, while the right section displays the tabs you can add.<\/p>\n To customize the ribbon, you have several options:<\/p>\n To add a new tab, click on New Tab<\/strong> in the right section and give it a name. You can also remove tabs, groups, or commands from the ribbon. Select the item you want to remove in the left section and click Remove.<\/p>\n To change the order of tabs and groups, select the item in the left section and use the Up and Down buttons to rearrange them.<\/p>\n Click OK in the Excel Options window to save your changes and apply the customized ribbon.<\/p>\n To extend Excel\u2019s functionality even further, you can customize the ribbon with additional applications by clicking on the Add-ins button in the Home tab.<\/p>\n\n Note: Customizing the ribbon is specific to your Excel installation and won\u2018t affect other users\u2019 ribbons.<\/p>\n Even though spreadsheets aren\u2019t always the most exciting things to look at, you can still take the time to make them easier to read by wrapping text.<\/p>\n Doing this lets you display multiple lines of text within a single cell. It’s convenient when you need to include line breaks or break up paragraphs of information within a cell without increasing the row height.<\/p>\n Select the cell(s) with the text you want to wrap. Navigate to the toolbar at the top of the Excel window and locate the Wrap Text button (an icon with an angled arrow). It is typically found in the Alignment section. Then, click on Wrap Text.<\/p>\n Give your spreadsheets a little personal touch by adding emojis.<\/p>\n To start, click on the cell where you want to insert an emoji. Then, open the emoji keyboard. This step may vary based on your operating system.<\/p>\n Windows.<\/strong> Use the keyboard shortcut Win + . or Win +<\/strong> ; to open the emoji keyboard. <\/p>\n macOS.<\/strong> Use the keyboard shortcut Ctrl + Cmd + Space<\/strong> to access the emoji keyboard. <\/p>\n Browse the available emojis and click on the one you want to insert. The selected emoji should now appear in the selected cell.<\/p>\n\n Emojis may appear small by default in Excel cells. To make them larger and improve visibility, you can adjust the cell size by dragging the row height and column width accordingly.<\/p>\n You can also copy emojis from external sources on the web or other applications and paste them directly into Excel cells.<\/p>\n Note<\/strong>: The ability to use emojis in Excel depends on the version of Excel and the device you are using. Some older versions or platforms may not support emojis or display them correctly. Therefore, it’s essential to ensure compatibility with the Excel version and platform you are working with.<\/p>\n <\/a> <\/p>\n Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet?<\/p>\n I’m glad you asked. There are a ton of Excel shortcuts<\/a> out there, including some of our favorites listed below.<\/p>\n PC: Ctrl-N | Mac: Command-N<\/p>\n PC: Shift-Space | Mac: Shift-Space<\/p>\n PC: Ctrl-Space | Mac: Control-Space<\/p>\n PC: Ctrl-Shift-Down\/Up | Mac: Command-Shift-Down\/Up<\/p>\n PC: Ctrl-Shift-Right\/Left | Mac: Command-Shift-Right\/Left<\/p>\n PC: Ctrl-K | Mac: Command-K<\/p>\n PC: Ctrl-1 | Mac: Command-1<\/p>\n PC: Alt-= | Mac: Command-Shift-T<\/p>\n <\/a> <\/p>\n How to Make a Chart or Graph in Excel [With Video Tutorial]<\/a>What is Excel?<\/h2>\n
Excel Basics<\/h2>\n
\n Executing basic computations like adding, subtracting, multiplying, and dividing.
\n Writing and formatting column text and titles.
\n Using Excel’s auto-fill features.
\n Adding or deleting single columns, rows, and spreadsheets.
\n Keeping column and row titles visible as you scroll past them in a spreadsheet so you know what data you’re filling in as you move further down the document.
\n Sorting your data in alphabetical order. <\/p>\nHow to Use Excel<\/h2>\n
Documents You Can Create in Excel<\/h3>\n
Excel Formulas<\/h3>\n
Excel Functions<\/h3>\n
1. Use Pivot tables to recognize and make sense of data.<\/h3>\n
2. Add more than one row or column.<\/h3>\n
3. Use filters to simplify your data.<\/h3>\n
4. Remove duplicate data points or sets.<\/h3>\n
5. Transpose rows into columns.<\/h3>\n
6. Split up text information between columns.<\/h3>\n
\n \u201cFixed Width\u201d means you want to select the exact location on all the columns that you want the split to occur. <\/p>\n7. Use formulas for simple calculations.<\/h3>\n
\n To subtract, use the – sign.
\n To multiply, use the * sign.
\n To divide, use the \/ sign. <\/p>\n8. Get the average of numbers in your cells.<\/h3>\n
9. Use conditional formatting to make cells automatically change color based on data.<\/h3>\n
10. Use the IF Excel formula to automate certain Excel functions.<\/h3>\n
11. Use dollar signs to keep one cell’s formula the same regardless of where it moves.<\/h3>\n
12. Use the VLOOKUP function to pull data from one area of a sheet to another.<\/h3>\n
13. Use INDEX and MATCH formulas to pull data from horizontal columns.<\/h3>\n
\n 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 with a lookup column to the right of the results column, then you’d have to rearrange those columns to do a VLOOKUP. This can be tedious with large datasets and\/or lead to errors. <\/p>\n14. Use the COUNTIF function to make Excel count words or numbers in any range of cells.<\/h3>\n
15. Combine cells using an ampersand (&).<\/h3>\n
16. Add checkboxes.<\/h3>\n
17. Hyperlink a cell to a website.<\/h3>\n
18. Add drop-down menus.<\/h3>\n
19. Use the format painter.<\/h3>\n
20. Create tables with data.<\/h3>\n
\n Filter drop-down arrows appear in the header row, allowing you to filter data within the table easily.
\n The table is formatted with alternating row colors, making it visually appealing.
\n Total rows are automatically added at the bottom of each column, allowing you to perform calculations like sum, average, etc., for the data in that column. <\/p>\n21. Use tables to conduct a what-if analysis.<\/h3>\n
22. Make formulas easier to comprehend with named ranges.<\/h3>\n
23. Group data to improve organization.<\/h3>\n
24. Use Find & Select to streamline formatting.<\/h3>\n
25. Protect your work.<\/h3>\n
Protect a Worksheet<\/h4>\n
\n Click Protect Sheet<\/strong>.
\n A Manage Protection dialog box will appear. There, you can select whether or not you want to protect the sheet. Set a password if desired and choose the options you wish to apply, such as preventing users from making changes to cells, formatting, inserting\/deleting columns or rows, etc. <\/p>\nProtecting a Workbook<\/h4>\n
26. Create custom number formats.<\/h3>\n
\n To display a specific text or character alongside numbers, use the @ <\/strong>symbol. For example, $0 <\/strong>will display a dollar sign before the number.
\n To display percentages, use the % <\/strong>symbol. For example, 0%<\/strong> will display the number as a percentage.
\n To create custom date or time formats, use codes such as dd<\/strong> for day, mm<\/strong> for month, yy<\/strong> for two-digit year, hh<\/strong> for hours, mm<\/strong> for minutes, and ss<\/strong> for seconds. For example, dd\/mm\/yyyy<\/strong> will display the date in the day\/month\/year format. <\/p>\n27. Customize the Excel ribbon.<\/h3>\n
\n To add a group within an existing tab, select the tab in the left section, click New Group<\/strong> in the right section, and name it.
\n To add commands to a group, select the group in the right section, choose commands from the left section, and click Add. You can also customize the order of the commands using the Up and Down buttons. <\/p>\n28. Improve visual presentation with text wrapping.<\/h3>\n\n
29. Add emojis.<\/h3>\n
Excel Keyboard Shortcuts<\/h2>\n
Create a New Workbook<\/strong><\/h4>\n
Select Entire Row<\/strong><\/h4>\n
Select Entire Column<\/strong><\/h4>\n
Select the Rest of the Column<\/strong><\/h4>\n
Select the Rest of the Row<\/strong><\/h4>\n
Add Hyperlink<\/strong><\/h4>\n
Open Format Cells Window<\/strong><\/h4>\n
Autosum Selected Cells<\/strong><\/h4>\n
Other Excel Help Resources<\/h2>\n
\n Design Tips to Create Beautiful Excel Charts and Graphs<\/a>
\n Totally Free Microsoft Excel Templates That Make Marketing Easier<\/a>
\n How to Learn Excel Online: Free and Paid Resources for Excel Training<\/a> <\/p>\n