- You use the CONCATENATE function to seamlessly merge text, numbers, and dates into a single cell for better organization.
- Excel lets you use VLOOKUP, INDEX/MATCH, and AVERAGEIFS to pinpoint specific data, calculate averages based on criteria, and gain valuable insights from your datasets.
- You can clean and organize spreadsheets using TEXT and IFERROR to format your data for clarity.
1. CONCATENATE
=CONCATENATE is one of the most crucial functions for data analysis as it allows you to combine text, numbers, dates, etc., from multiple cells into one. To use this function, follow the syntax below:
=CONCATENATE(Cell 1, Cell 2)
Cell 1 and Cell 2 refer to the cells with values you want to merge. It is also handy for creating the tracking parameters for marketing campaigns, building API queries, adding text to a number format, and several other things.
2. LEN
=LEN is another handy function for data analysis that essentially outputs the number of characters in any given cell. The function is predominantly usable while creating title tags or descriptions with a character limit. The syntax is below:
=LEN(Cell)
Cell in the syntax above may have a value such as A1, B2, etc. The function can also be useful when discovering the differences between different unique identifiers, which are often lengthy and not in the correct order.
3. VLOOKUP
=VLOOKUP can match data from a table with an input value. The function offers two matching modes — Exact and Approximate — controlled by the lookup range. If you set the range to FALSE , it’ll look for an exact match, but if you set it to TRUE , it’ll look for an approximate match. Below is the syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The elements of this function are broken down as shown:
- Lookup_value : It is the element you need to search for.
- Table_array : Range of cells containing the data you want to search.
- col_index_num : Column number in the table_array containing the value you want to return.
- [range_lookup] : Optional argument specifying how the function finds the lookup_value. TRUE for approximate matches, FALSE for exact matches.
4. INDEX/MATCH
Like the VLOOKUP function, the INDEX and MATCH functions are handy for searching specific data based on an input value.
When used together, the INDEX and MATCH can overcome the VLOOKUP’s limitations in delivering the wrong results (if you are not careful). So, when you combine these two functions, they can pinpoint the data reference and search for a value in a single-dimension array. That returns the coordinates of the data as a number. Below is the syntax:
=INDEX(column of the data you want to return, MATCH(common data point you're trying to match, column of the other data source that has the common data point, 0))
In the example above, I wanted to look up the number of views in January. For that, I used the formula:
=INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3)
Here,
- A2:C13 is the column of data I want the formula to return.
- Jan is the value I want to match.
- A2:A13 is the column in which the formula will find Jan .
- 0 signifies that I want the formula to find an exact match for the value.
If you want to find an approximate match, you must substitute the 0 with 1 or -1 . So, 1 will find the largest value less than or equal to the lookup value, and -1 will find the smallest value less than or equal to the lookup value. Note that if you don’t use 0, 1, or -1, the formula will use 1 by default.
5. MINIFS/MAXIFS
=MINIFS and =MAXIFS are similar to the =MIN and =MAX functions, except they allow you to take the minimum/maximum set of values and match them on particular criteria. So, essentially, the function looks for the minimum/maximum values and matches them with input criteria. Here is the syntax:
=MINIFS(min_range, criteria_range1, criteria1,…)
=MAXIFS(max_range, criteria_range1, criteria1,…)
In the example above, I wanted to find the minimum scores based on the student’s gender. For that, I used the formula:
=MINIFS (C2:C10, B2:B10, "M")
I got the result 27. Here,
- C2:C10 is the column in which the formula will look for the scores.
- B2:B10 is a column in which the formula will look for the criteria (the gender).
- M is the criteria.
Similarly, I used the formula below for maximum scores and got the result 100.
=MAXIFS(C2:C10, B2:B10, "M")
6. AVERAGEIFS
The =AVERAGEIFS function allows you to find an average for a particular data set based on one or more criteria. While using this function, you should remember that each criterion and average range can be different.
However, in the =AVERAGEIF function, the criteria and sum range must have the same size range. Below is the syntax:
=AVERAGEIFS(average_range, criteria_range1, criteria1,…)
In the example above, I wanted to find the average score based on the student’s gender. For that, I used the formula below and got 56.8. Here,
- C2:C10 is the range in which the formula will look for the average.
- B2:B10 is the criteria range.
- M is the criteria.
=AVERAGEIFS(C2:C10, B2:B10, "M")
7. COUNTIFS
If you want to count the instances where a data set meets specific criteria, you must use the =COUNTIFS function. This function allows you to add limitless criteria to your query, making it the easiest way to find the count based on the input criteria. Here is the syntax:
=COUNTIFS(criteria_range1, criteria1,…)
In this example, I wanted to find the number of male or female students who got passing marks (i.e., >=40). For that, I used the formula below:
=COUNTIFS(B2:B10, "M", C2:C10, ">=40")
Here,
- B2:B10 is the range in which the formula will look for the first criteria (gender).
- M is the first criterion.
- C2:C10 is the range in which the formula will look for the second criterion (marks).
=40 is the second criterion.
8. SUMPRODUCT
The =SUMPRODUCT function helps you multiply ranges or arrays together and then returns the sum of the products. It’s a versatile function and can be used to count and sum arrays like COUNTIFS or SUMIFS, but with added flexibility. You can also use other functions within SUMPRODUCT to extend its functionality further. Here is the syntax:
=SUMPRODUCT(array1, [array2], [array3],…)
In this example, I wanted to find the total of all the products sold. I used the formula:
=SUMPRODUCT(B2:B8, C2:C8)
Here,
- B2:B8 is the first array (the quantity of products sold).
- C2:C8 is the second array (the price of each product). The formula then multiplies the quantity of each product sold by its price and adds all of it up to deliver the total sales.
9. TRIM
The =TRIM function is particularly useful when working with a data set with several spaces or unwanted characters. The function allows you to easily remove these spaces or characters from your data and get accurate results while using other functions. Here is the syntax:
=TRIM(text)
In this example, I wanted to remove all the extra spaces between the words Mouse and pad in A7 . For that, I used the formula:
=TRIM(A7)
10. FIND/SEARCH
Rounding things off are the FIND/SEARCH functions, which will help you isolate specific text within a data set. Both the functions are similar in what they do, except for one major difference — the =FIND function only returns case-sensitive matches. Meanwhile, the =SEARCH function has no such limitations. Here is the syntax for Find and Search, respectively:
=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])
In this example, I wanted to find the number of times Gui appeared within Guiding Tech , so I used the formula below, which delivered the result 1.
=FIND(A2, B2)
If I wanted to find the number of times gui appeared within Guiding Tech instead, I’d have to use the =SEARCH formula because it isn’t case-sensitive. These functions are particularly useful when looking for anomalies or unique identifiers.
11. TEXT
The =TEXT function transforms numbers into text. This makes it possible to display them in a specific format. You may use this function to ensure clarity and consistency. Here’s the syntax:
=TEXT(value, "format_text")
In the example above, I use the formula below to convert the value in G1 to words.
=TEXT(G1, "mmmm d, yyyy")
12. IFERROR
The =IFERROR function is an error handler that allows you to specify what you want the Excel program to show when there is an error. It helps avoid a lot of # error signs on your sheets. Here’s the syntax:
=IFERROR(value, value_if_error)
In the example above, we use the formula below to show a message when there is an error.
=IFERROR(A1/B1, "Division by zero error")
13. FILTER
The =FILTER function extracts specific data sets based on one or more criteria. You can dynamically filter results without making changes to the data. Hence, you can explore and analyze different subsets of your data. Here’s the syntax:
=FILTER(array, include, [if_empty])
We can use the example below to show column B values equal to Sales .
=FILTER(A1:C10, B1:B10 = "Sales")
14. SORT
The =SORT function organizes data in ascending or descending order. Here’s the syntax:
=SORT(array, sort_col1, sort_order1, [sort_col2, sort_order2], ...)
In the example below, we sort data in the A1:C10 range, 3 refers to the third column in the range, and we use -1 to sort in descending order.
=SORT(A1:C10, 3, -1)
You may also find some Excel shortcuts handy while using these functions.
Was this helpful?
- Corrupted Excel files, faulty add-ins, and outdated printer drivers are some of the most common causes of this issue.
- Try printing another Excel file to ensure the issue is not limited to a specific spreadsheet.
- You can try running the Microsoft Office repair tool if nothing else works.
Fix 1: Save Your Excel File in XPS Format and Try Again
If Excel can’t respond to print requests, save your file in the XPS format and try again. Several users on Microsoft Community post reported fixing the issue with this simple workaround. So, if you’re in a rush, try this method.
Step 1: Open the Excel file you wish to print and click the File menu at the top left corner.
Step 2: Navigate to the Print tab and use the drop-down menu under Printer to select Microsoft XPS Document Writer . Then, click the Print button.
Step 3: When the Save Print Output As dialog appears, save your Excel file in the XPS format. It should print without issues.
Fix 2: Open Excel in Safe Mode
You can try printing an Excel file in safe mode to see if one of the third-party add-ins is causing the problem. For that, press the Windows key + R to open the Run dialog. Type excel -safe in the box and press Enter .
Check to see if Excel prints your file in safe mode. If it does, one of the third-party add-ins is to blame. You can disable all add-ins and re-enable them individually to isolate the culprit.
Step 1: Open Microsoft Excel and go to File > Options .
Step 2: In the Excel Options window, switch to the Add-ins tab from the left column. Select COM Add-ins in the Manage drop-down menu and click the Go button.
Step 3: Uncheck all the Add-ins and click OK .
After this, restart Excel and enable your add-ins one at a time. Print a test page after enabling each add-in to identify the one causing the issue.
Fix 3: Update Printer Driver
Issues with your printer driver can affect Excel’s ability to print spreadsheets and lead to problems. To avoid this, you should ensure that your printer driver is up to date and functioning properly.
Step 1: Right-click on the Start icon and select Device Manager from the menu that appears.
Step 2: Double-click on Print queues to expand it. Right-click on your printer and select Update driver .
Follow the on-screen prompts to finish updating the printer drivers. After that, try printing your file again.
Fix 4: Remove and Reinstall Your Printer
If Microsoft Excel still can’t print, try removing your printer and re-adding it on Windows. Here’s how to do it.
Step 1: Press the Windows key + I to open the Settings app.
Step 2: Select Bluetooth & devices from the left sidebar and go to Printers & scanners .
Step 3: Select your printer from the list.
Step 4: Click the Remove button at the top to delete your printer.
Step 5: After that, return to the Printers & scanners menu and click on Add device . Then, follow the on-screen prompts to add your printer again.
Fix 5: Repair Microsoft Office
Microsoft Office offers a handy repair tool for any issues with Office apps. If nothing else works, consider repairing Microsoft Office by following the steps below.
Step 1: Right-click on the Start icon and select Installed apps from the list.
Step 2: Scroll down to locate the Microsoft Office product on the list. Click the three-dot menu icon next to it and select Modify .
Step 3: Select Online Repair and hit Repair .
