Important: Be sure to type an equal sign (=) in the cell before you type the numbers and the / operator; otherwise, Excel will interpret what you type as a date. For example, if you type 7/30, Excel may display 30-Jul in the cell. Or, if you type 12/36, Excel will first convert. 23:59 My excel sheet is interpreting as 5 December 2020 23:59 however it is meant to be 12 may 2020 23:59. It is data that is downloaded and both time and date come a single entry in the table I download. Is there any way to convert it to be correct. I have now been trying for over 5 hours!! Help is needed please. Best regards, David.
There are over 475 functions in Excel. This can make it overwhelming when you are getting started with data analysis.
With such a large variety of functions, it can be difficult to know which one to use for specific Excel tasks.
The most useful Excel functions are those that make the task seem easy. And the good news is that most Excel users have a toolkit of just a few functions that complete most of their needs.
This resource covers the 12 most useful Excel functions for data analysis. These functions provide you with the tools to handle the majority of your Excel data analysis tasks.
Download your free practice workbook
Don't forget to download the exercise file to help you follow along with this article and learn the best functions for data analysis.
Learn the most useful Excel functions
Download your FREE exercise file to follow along
1. IF
Excel 123
The IF function is extremely useful. This function means we can automate decision making in our spreadsheets.
With IF, we could get Excel to perform a different calculation or display a different value dependent on the outcome of a logical test (a decision).
The IF function asks you for the logical test to perform, what action to take if the test is true, and the alternative action if the result of the test is false.
=IF(logical test, value if true, value if false)
In this example, we have displayed the word “Yes” if the delivery date in column C is more than 7 days later than the order date in column B. Otherwise, the word “No” is displayed.
=IF(D2>7,'Yes','no')
2. SUMIFS
SUMIFS is one of the most useful Excel functions. It sums values that meet specified criteria.
Excel also has a function named SUMIF which does the same task except it can only test one condition, while SUMIFS can test many.
So you can essentially ignore SUMIF as SUMIFS is a superior function.
The function asks you for the range of values to sum, and then each range to test and what criteria to test it for.
=SUMIFS(sum range, criteria range 1, criteria 1, …)
In this example, we are summing the values in column C for the region entered into cell E3.
=SUMIFS(C2:C9,B2:B9,E3)
It is definitely worth exploring the SUMIFS function in more detail. It is an extremely useful Excel function.
3. COUNTIFS - mention averageifs, maxifs
The COUNTIFS function is another mega function for Excel data analysis.
It is very similar to the SUMIFS function. And although not mentioned as part of the 12 most useful Excel functions for data analysis, there are also AVERAGEIFS, MAXIFS, and MINIFS functions.
The COUNTIFS function will count the number of values that meet specified criteria. It, therefore, does not require a sum range like SUMIFS.
=COUNTIFS(criteria range 1, criteria 1, …)
In this example, we count the number of sales from the region entered into cell E3 that have a value of 200 or more.
=COUNTIFS(B2:B9,E3,C2:C9,'>=200”)
When using the SUMIFS and COUNTIFS functions, the criteria must be entered as text or as a cell reference. This example uses both techniques in the same formula.
4. TRIM
This brilliant function will remove all spaces from a cell except the single spaces between words.
The most common use of this function is to remove trailing spaces. This commonly occurs when content is pasted from somewhere else or when users accidentally type spaces at the end of text.
In this example, the COUNTIFS function from before is not working because a space has been accidentally used at the end of cell B6.
Users cannot see this space, which means it is not identified until something stops working.
The TRIM function will prompt you for the text to remove spaces from.
=TRIM(text)
In this example, the TRIM function is used in a separate column to clean the data in the region column ready for analysis.
=TRIM(B2)
The COUNTIFS function then has clean data and works correctly.
5. CONCATENATE
The CONCATENATE functioncombines the values from multiple cells into one.
This is useful for piecing together the different parts of text such as someone's name, an address, a reference number or a file path or URL.
It prompts you for the different values to use.
=CONCATENATE(text1, text2, text3, …)
In this example, CONCATENATE is used to combine the firstname and lastname into a fullname. A space is entered for the text2 argument.
=CONCATENATE(A2,' ',B2)
6. LEFT/RIGHT
The LEFT and RIGHT functions will do the opposing action of CONCATENATE. They will extract a specified number of characters from the start and end of text.
This can be used to extract parts of an address, URL, or reference for further analysis.
The LEFT and RIGHT functions request the same information. They want to know where the text is and how many characters you want to extract.
=LEFT(text, num chars)
=RIGHT(text, num chars)
In this example, column A contains a reference that is made up of the client ID (first two characters), a transaction ID, and then the region code (final character).
The following LEFT function is used to extract the client ID.
=LEFT(A2,2)
The RIGHT function can be used to extract the last character from the cells in column A. This example indicates whether the client is in the South or the North.
=RIGHT(A2,1)
7. VLOOKUP
The VLOOKUP function is one of the most commonly used and recognizable functions in Excel.
It will look for a value in a table and return information from another column relating to that value.
It is great for combining data from different lists into one or comparing two lists for matching or missing items. It is an important tool in Excel data analysis.
It prompts for four pieces of information:
- The value you want to look for
- Which table to look in
- Which column has the information you want to return
- What type of lookup you would like to perform.
=VLOOKUP(lookup value, table array, column index number, range lookup)
In this example, we have a table containing sales from our employees. There is another table with further information about these employees (tables are kept small for the example).
We would like to bring the data showing which region the employee is based into the sales table for analysis.
The following formula is used in column D:
=VLOOKUP(B2,$G$2:$H$12,2,FALSE)
This can be one of the more difficult functions to learn for beginners to Excel formulas. You can learn VLOOKUP more in-depth in this article, or from our comprehensive Excel course.
8. IFERROR
Sometimes errors happen that may be innocent and sometimes these errors may be things you can predict. The VLOOKUP function from before is a typical example of this.
We have an error because there is a typo in the name in the sales table. This means that VLOOKUP cannot find that name and produces an error.
Using IFERROR we could display a more meaningful error than the one Excel provides, or even perform a different calculation.
The IFERROR function requires two things. The value to check for the error and what action to perform instead.
In this example, we wrap the IFERROR function around VLOOKUP to display a more meaningful message.
=IFERROR(VLOOKUP(B2,$G$2:$H$12,2,FALSE),'Name not found. Check both lists')
9. VALUE
Often the set of data you need to analyze has been imported from another system or copied and pasted from somewhere.
This can often lead to data being in the wrong format, such as a number being stored as text. You cannot perform data analysis tasks such as sum if Excel does not recognize them as a number.
Fortunately, the VALUE function is here to help. Its job is to convert numbers stored as text to numbers.
The function prompts for the text to convert.
=VALUE(text)
In this example, the following formula converts the sales values stored as text in column B to a number.
=VALUE(B2)
10. UNIQUE
The UNIQUE function is a new function available to those using the Microsoft 365 version only.
The function wants to know three things:
- The range to return the unique list from
- Whether you would like to check for unique values by column or by row
- Whether you want a unique list, or a distinct list (items that occur only once).
=UNIQUE(array, by col, exactly once)
In this example, we have a list of product sales and we want to extract a unique list of the product names. For this, we only need to provide the range.
=UNIQUE(B2:B15)
This is a dynamic array function and therefore spills the results. The blue border indicates the spilled range.
We can then use the SUMIFS function, mentioned earlier in this article, to sum the sales for each of those products.
This should look familiar to earlier. However, a # was used to reference the spilled range this time.
11. SORT
This is another function only available to Microsoft 365 subscribers. As the name suggests, it will sort a list.
The SORT function prompts for four arguments:
- The range to sort
- Which column to sort the range by
- What order to sort the range (ascending or descending)
- Whether to sort the rows or the columns.
=SORT(array, sort index,sort order, by col)
This is fantastic. And it can be used with the previous UNIQUE example to sort the product names in order.
For this, we only need to provide it with the range to sort.
=SORT(UNIQUE(B2:B15))
12. FILTER
Following the SORT function, there is also a function to filter a list. Another function only available to Microsoft 365 users.
This function will filter a range. This is an extremely powerful function and is a dream for analyzing data and producing reports.
Excel 12 Hour Time Format
The FILTER function takes three arguments:
- The range to filter
- The criteria that specifies which results to return
- What action to take if no results are returned.
=FILTER(array, include, if empty)
In this example, only the results for the subject entered in cell F2 are returned.
=FILTER(B2:C12,A2:A12=F2,'No scores')
Wrap Up
Learning the most useful Excel functions for data analysis mentioned in this article will go a long way to making Excel data analysis easier.
But there are still many more functions and also Excel features to learn to be a true data analysis whizz.
Two other essential Excel tools to master are Power Query and Power Pivot.
Power Query makes importing and transforming data for analysis a breeze. And Power Pivot is the perfect tool if you analyze large volumes of data. It can store huge volumes of data outside Excel and has its own formula language called DAX.
Take the Power Query and Power Pivot courses on GoSkills to fast track your data analysis skills in Excel today.
Level up your Excel skills
Become a certified Excel ninja with GoSkills bite-sized courses
Start free trialMicrosoft Excel has long been one of the main go-to options for spreadsheets and mathematical functions. But with new solutions that offer additional features, it’s time to take a look at the other choices you have available to you.
Depending on what you use Excel for, there could be hundreds of Excel alternatives across the web. These are twelve of the best, and easiest to use, to check out for you & your team.
1. Toggl Plan
Why You’ll Love It: Offering an innovative visual planning tool that is both powerful and easy to use, Toggl Plan is a great option if you’re looking for alternatives to Excel. The system allows you to replace spreadsheets as it helps manage workflow, track projects, organize tasks, as well as schedule meetings and other team assignments. It has a clear timeline that is color coded and provides simple sharing functions to keep everyone on the same page at all times.
Pros: The simple click-and-drag software makes project management processes a breeze from beginning to end.
Cons: You won’t find basic Excel functions like calculations. Instead, Toggl Plan would replace Excel if you’re using it for project / task / team planning, scheduling and other needs.
Price: Free for teams up to 5 people. Paid plans start at $8 per user per month.
Want to hear what others are saying about Toggl Plan? Check out our Capterra reviews, or this more detailed product review with screenshots & annotations by Markup Hero.
2. Google Sheets
Why You’ll Love It: Along with being the world’s largest search engine, Google also offers its users a wide range of other types of software including spreadsheets. With Google Sheets, you’ll have essentially all the same functionality found on Excel while having the added benefits of keeping everything safe and secure in your Google Drive cloud account. Easily share, edit, and update whenever and wherever you need.
Pros: User-friendly and easy to learn, especially if you’ve already been using Microsoft Excel.
Cons: Some users would prefer that Google Sheets offer analytics and reporting features to get more extensive history and details about their spreadsheets.
Price: Free. Paid plans start at $5 per month for additional storage when it becomes necessary.
3. Zendesk Explore
Why You’ll Love It: Since it acquired BIME Analytics back in 2015, Zendesk has grown its analytics and reporting capabilities into something that could replace much of the functionality of Excel. Their clear and customizable Explore dashboard makes it easy to review data and create reports based on your company’s needs.
Pros: Sharing with your entire team is incredibly simple to ensure everyone can see the information and provide input.
Cons: With most of their features focused on CRM software, you may find the Excel-style functions are limited to basic analytics.
Price: Free for Lite features. Additional functions available starting at $9 per user per month.
4. Workzone
Why You’ll Love It: Mainly focused on marketing agencies and operations companies, Workzone is project management software that also doubles as one of the best Excel alternatives. It can be used for budget management needs such as time and expense tracking as well as for monitoring resources and milestones. Managed on the cloud, users will be able to access it from anywhere using a computer, tablet, or mobile device.
Pros: Often praised for its collaboration options, you’ll likely enjoy the ability to keep everyone in the loop automatically as opposed to consistently sending updated Excel spreadsheets.
Cons: Occasional issues come up with the calendar features being less than intuitive, making it necessary to keep an eye on dates to ensure they are correct.
Price: Free demo. The price varies based on the size of your team. As an example, a team of 15 will start at $24 per user per month.
5. Zoho Sheet
Why You’ll Love It: Following suit with Google, the team at Zoho has created a massive supply of applications to replace functions across a wide variety of needs. Among their apps, Zoho Sheet is their realization of an effective spreadsheet software. It’s a great place to organize, discuss, and analyze data with your team as you create graphs and charts.
Pros: A strong option for companies that need to manage a large amount of data all in one place, Zoho Sheet easily imports from other software to get you up and running quickly.
Cons: Sometimes, spreadsheets may need mild reformatting after importing from Excel and other alternatives.
Price: Free for teams up to 5 people. Paid plans start at $4 per user per month.
6. Sheetster
Why You’ll Love It: Able to view both XLS and XLSX formats, Sheetster is a web-based spreadsheet editor with nearly all the same features that you’d find in Microsoft Excel. Used most commonly as a JavaScript, it is generally meant for developers who want to use these functions in their web apps or other software. It can be incorporated into everything from a mobile application to a CMS like WordPress.
Pros: Great for web development if you want to add it to your new website or other creation as a developer.
Cons: Without general coding knowledge, this option may not be easy to use for some people looking for a way to replace Excel.
Price: Free
7. Numbers (Mac)
Why You’ll Love It: If you’re an Apple user with a Mac, then Numbers is the ideal Excel alternative for you. It is nearly identical to its Microsoft counterpart when it comes to creating, editing, and managing spreadsheets. You can even use it across platforms meaning that you’ll be able to share your sheets with Windows users on your team.
Pros: For anyone who recently switched to Mac that previously used Excel on their PC, this is the perfect way to pick right back up where you left off.
Cons: Minor functionality differences from Excel that may require some research to learn.
Price: Starting at $9.99 per app depending on your operating system.
8. Redbooth
Why You’ll Love It: Joining the ranks of other project management applications on this list, Redbooth offers task organization, collaboration tools, and other functions. Users can manage members of their staff as they work on projects that are organized with columns that are customizable like Excel. Keep things organized on the cloud so everyone on your team can maintain the workflow.
Pros: Offers a simple interface that makes it easy to learn and even easier to use.
Cons: Though many types of software like this one have numerous integrations that can be used with it, the options for Redbooth are fairly limited.
Price: Free 14-day trial. Plans starting at $9 per user per month.
9. Scoro
Why You’ll Love It: While it is usually marketed as a project management platform, Scoro is another one of the main Excel alternatives worth considering. It is a business solution software with features that include financial management, time tracking, reporting, analytics, and more. While it lacks basic spreadsheet functionality, this program can be used in many other beneficial ways.
Pros: The software is highly adaptable to your specific industry as a means to make it fit your needs.
Cons: Onboarding can be difficult and it can take some time to learn how to use the software.
Price: Free 14-day trial. Plans starting at $26 per user per month.
10. Apache OpenOffice
Why You’ll Love It: The software for Apache OpenOffice is open source, which means it is free to use in a variety of ways. Users can apply it to their website, mobile app development, or simply integrate the software for basic spreadsheet needs. Dubbed a “productivity suite”, Apache can create equations, databases, multimedia presentations, a variety of diagrams, and even illustrations that can be rendered into 3D images.
Excel 12 Gauge Single Shot
Pros: Essentially provides all the same options as the full Microsoft Office package in one place for free.
Cons: It isn’t updated very often, which can occasionally lead to bugs and minor glitches in the software.
Price: Free
11. LibreOffice
Why You’ll Love It: LibreOffice is considered one of the top Excel alternatives and is powered by the team at OffiDocs. Similar to Apache OpenOffice, it uses open source software to view and edit spreadsheets from Microsoft platforms. You’ll find it has most of the same basic features found in Excel.
Pros: Similar layout to Microsoft that makes it easy to convert if you want to switch to this free alternative.
Cons: LibreOffice doesn’t have many of the formatting functions or high-level calculations that are offered in Excel and some of the other options on this list.
Price: Free
12. Spread32
Why You’ll Love It: If you’re on the hunt for the most basic of Excel alternatives, Spread32 could be just right for you. While it is less involved than most of the other options above, the software still has over 300 functions to provide the essentials many users require. While it doesn’t offer the colorful dashboards and other aesthetics of more contemporary apps, it will do what you need it to.
Pros: Spread32 is a simple, lightweight option that offers portability with its attached app of SpreadCE.
Cons: The software interface takes a while to learn because it has a different look and feel than Excel.
Price: Free
Which of These Excel Alternatives Will You Try?
From simple spreadsheets to complex project management applications, there are numerous Excel alternatives worth a try. Among the options mentioned above, you’re sure to find great software to replace it.
At the top of the list, Toggl Plan provides a great variety of features that will fill all of your time tracking, task management, and schedule planning needs. Learn more about our software and see how it will benefit your company.