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.
Don’t forget to download the example workbook.
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.
Download Example Workbook
Click here to download the 10 Excel formulas workbook. Use the sample data and completed formulas to learn all of these.
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
=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
Download Example Workbook
Click here to download the 10 Excel formulas workbook. Use the sample data and completed formulas to learn all of these.
Something else?
Do you want to learn more? Please share your questions or suggestions in the comments box. I will create a post or video to help you.
Also check out the Top 10 formulas for data analysis page for few more ideas.
One Response to “SQL vs. Power Query – The Ultimate Comparison”
Enjoyed your SQL / Power Query podcast (A LOT). I've used SQL a little longer than Chandoo. Power Query not so much.
Today I still use SQL & VBA for my "go to" applications. While I don't pull billions of rows, I do pull millions. I agree with Chandoo about Power Query (PQ) lack of performance. I've tried to benchmark PQ to SQL and I find that a well written SQL will work much faster. Like mentioned in the podcast, my similar conclusion is that SQL is doing the filtering on the server while PQ is pulling data into the local computer and then filtering the data. I've heard about PQ query folding but I still prefer SQL.
My typical excel application will use SQL to pull data from an Enterprise DB. I load data into Structured Tables and/or Excel Power Pivot (especially if there's lot of data).
I like to have a Control Worksheet to enter parameters, display error messages and have user buttons to execute VBA. I use VBA to build/edit parameters used in the SQL. Sometimes I use parameter-based SQL. Sometimes I create a custom SQL String in a hidden worksheet that I then pull into VBA code (these may build a string of comma separated values that's used with a SQL include). Another SQL trick I like to do is tag my data with a YY-MM, YY-QTR, or YY-Week field constructed form a Transaction Date.
In an application, I like to create a dashboard(s) that may contain hyperlinks that allow the end-user to drill into data. Sometimes the hyperlink will point to worksheet and sometimes to a supporting workbook. In some cases, I use a double click VBA Macro that will pull additional data and direct the user to a supplemental worksheet or pivot table.
In recent years I like Dynamic Formulas & Lambda Functions. I find this preferable to pivot tales and slicers. I like to use a Lambda in conjunction with a cube formula to pull data from a power pivot data model. I.E. a Lambda using a cube formula to aggregate Accounting Data by a general ledger account and financial period. Rather than present info in a power pivot table, you can use this combination to easily build financial reports in a format that's familiar to Accounting Professionals.
One thing that PQ does very well is consolidating data from separate files. In the old days this was always a pain.
I've found that using SQL can be very trying (even for someone with experience). It's largely an iterative process. Start simple then use Xlookup (old days Match/Index). Once you get the relationships correct you can then use SQL joins to construct a well behaved SQL statement.
Most professional enterprise systems offer a schema that's very valuable for constructing SQL statements. For any given enterprise system there's often a community of users that will share SQL. I.E. MS Great Plains was a great source (but I haven't used them in years).
Hope this long reply has value - keep up the good work.