Search

# Top 10 Excel formulas for IT people

Share

Are you AND(“In IT”, “Use Excel”)? Then this article is for you. Here is my list of top 10 Excel formulas for people in IT. As software miners use Excel differently than other folks, this article highlights important use cases for you. These examples will particularly help project managers, IT analysts, testing people and business analysts.

## Video - Top 10 Excel formulas for IT people

If you don’t want to read the article, watch this video for my recommended top 10 Excel formulas for IT people.

## Sample data for these formulas

For most situations in these top 10 examples, I will be using the below two column dataset (named testing). This shows number of test cases completed on daily basis for the month of May 2020.

## 1. Count values between dates

Let’s say you want to count how many test cases were completed between dates 6-May-2020 and 16-May-2020.

You can use below formula:

=SUMIFS(testing[Test cases done],

testing[Date],”>=6-May-2020″,testing[Date],”<=16-May-2020″)

To see all testcase data for the given date range in cells J4 & J5, you can use FILTER function.

=FILTER(testing, (testing[Date]>=J4)*(testing[Date]<=J5))

## 2. Lookup a value

Given the test cases data, you want to know how many cases were done on a given date – 18-May-2020?

You can use either XLOOKUP or VLOOKUP for this. I recommend using XLOOKUP if you are running Excel 365.

Refer to sample formulas below.

=XLOOKUP(“18-May-2020”,testing[Date],testing[Test cases done])

=VLOOKUP((“18-May-2020”,testing,2,FALSE)

## 3. Gap between two dates

Dates & time values are used often in IT situations (project planning, estimation etc.) To calculate the gap between two dates, simply subtract one from another. Like this:

=date2-date1

## 4. Working days between two dates

What if you want to calculate the FTE (full time equivalent) for a project module. You can use NETWORKDAYS formula to find out the number of working days between two dates and multiply that with headcount to get FTE required.

Something like this:

Number of working days =NETWORKDAYS(date1, date2)

Number of working days with Friday, Saturday weekend =NETWORKDAYS.INTL(date1, date2, 7)

Working days for 4 day workweek (MTWT) =NETWORKDAYS.INTL(date1, date2,”0000111″)

The pattern 0000111 refers to Monday to Thursday as working days (0) and Friday to Sunday as weekend (1).

## 5. Nth smallest item

We can use MIN() to find the smallest item in any data (and MAX() for largest). But what if you want to find the 2nd smallest item or 4th largest one?

You can use SMALL() and LARGE() formulas for this purpose. See below examples:

2nd smallest number of cases in any day  =SMALL(testing[Test cases done],2)

3rd highest number of cases in any day

=LARGE(testing[Test cases done],3)

Which date had 2nd smallest number of cases completed?

=XLOOKUP(SMALL(testing[Test cases done],2),
testing[Test cases done],testing[Date])

## 6. One week moving average

Moving averages help with understanding the trend. Let’s say you want to know what is the 7 day moving average of test cases done? You can use AVERAGE formula with relative references, as illustrated below.

## 7. Extract portions from text

You can use Excel’s flash fill to extract portions of text on one time basis. If you want to make it more dynamic then you must learn the text formulas. There are many, but start with these 4.

• FIND – to find the position of one text in another
• LEFT – to get part of text from left
• RIGHT – to get part of text from right
• MID – to get text from the middle

Here is an example to extract user name from email address in cell J4 (john.doe@acme.com)

=LEFT(J4, FIND(“@”, J4)-1)

## 8. Combine text values

If you want to combine a bunch of values but include a separator in between, use TEXTJOIN function. Here is an example to combine email addresses in cells J4:J8

=TEXTJOIN(“, “,,J4:J8)

## 9. Generate random data

Excel has many useful functions to create random data. If you just want a random number between two values, use the RANDBETWEEN() function. To generate a bunch of random values, use the newly introduced RANDARRAY() function. See these examples:

10 Random whole numbers between 1 & 100:

=RANDARRAY(10,,1,100,TRUE)

A random phone number:

=RANDBETWEEN(1111111111, 9999999999)

8 random dates in year 2020:

=RANDARRAY(8,,”1-JAN-2020″,”31-DEC-2020″,TRUE)

Other examples of random data in Excel:

## 10. Avoid errors

Just as you want to have bug free code, your Excel files should also be devoid of errors. You can do this in below ways:

• Use if not found option with functions like XLOOKUP, FILTER, XMATCH etc.
• Use IFERROR to print alternative message when your formula has error
• Check errors with ISERROR function

## Something else?

Also check out the Top 10 formulas for data analysis page for few more ideas.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Ever wanted to automatically format values in thousands, millions or billions in Excel? In this article, let me show you two powerful techniques to do just that.

## Related Tips

Charts and Graphs

Learn Excel

Learn Excel

Learn Excel

Excel Howtos

### How to fix SPILL Error in Excel Tables (3 easy solutions)

Financial Modeling

### 9 Responses to “Top 10 Excel formulas for IT people”

1. Richard says:

Text Join or &

cell1 cell2 formula cell output/result
splen did =cell1&cell2 splendid
even better =cell1&" "&cell2 even better

2. Daniel says:

Thanks Chandoo! Please give more input for people from IT Industry. Great work!

3. Martin says:

Wow this is so wrong....

• Chandoo says:

Hi Martin...

I created the list of examples based on what formulas I would often use when I worked as Business analyst & project lead. What other formulas do you normally use?

4. Daniel says:

Why we need the & before refering the cell in the first formula? We are already in the and statement why we need the &? It's not clear for me. Thanks.

5. DOUG GALT says:

formula error on this excel sheet

6. ExcelPath says:

Sum values between range and Remove duplicate using formula.

7. Paul Williams says:

Hi Chandoo,

I want to retrieve the winning column of a pivot table to assign a user that has multiple sites in multiple regions.
This pivot table is collecting number of sites in each of 3 regions; C,E,W. There should be one winner defined as most sites. If there is a tie, the region to the left may be the winner.

email C E W Grand Total

Email1 54 42 14 110
Email2 56 6 34 96
Email3 53 6 34 93
Email4 19 34 7 60
Email5 13 28 18 59
Email6 12 27 17 56
Email7 19 31 5 55
Email8 13 24 17 54
Email9 9 35 2 46
Email10 37 4 41
Email11 10 14 24
Email12 10 14 24
Email13 3 11 10 24
Email14 19 2 21
Email15 9 12 21
Email16 3 14 4 21
Email17 7 7 6 20
Email18 12 7 19
Email19 6 10 1 17
I need a formula that will assign the Value C, E, or W to each Email line please?

8. John says:

It would be helpful to anotate which Excel Version is min. required to run a workbook.

Some of the formulas do not work with Excel 2016 (e. g.), because the use features from 2019 or 365.

But even that: great work and great content.

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.