40 Essential Excel Formulas for Aspiring Data Analysts

Deepshika

40 𝐄𝐬𝐬𝐞𝐧𝐭𝐢𝐚𝐥 𝐄𝐱𝐜𝐞𝐥 𝐅𝐨𝐫𝐦𝐮𝐥𝐚𝐬 𝐟𝐨𝐫 𝐀𝐬𝐩𝐢𝐫𝐢𝐧𝐠 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐭𝐬

Here is the list of 40 𝐄𝐬𝐬𝐞𝐧𝐭𝐢𝐚𝐥 𝐄𝐱𝐜𝐞𝐥 𝐅𝐨𝐫𝐦𝐮𝐥𝐚𝐬 𝐟𝐨𝐫 𝐀𝐬𝐩𝐢𝐫𝐢𝐧𝐠 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐭𝐬:

Here are 40 essential Excel formulas you should know to ace your next Data Analyst interview:

1. 𝐓𝐞𝐱𝐭 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬

𝐋𝐄𝐍: Returns the length of a text string.

𝐓𝐑𝐈𝐌: Removes extra spaces from text.

𝐂𝐎𝐍𝐂𝐀𝐓: Joins multiple text strings into one.

𝐒𝐄𝐀𝐑𝐂𝐇: Finds a substring within a text string (case-insensitive).

𝐅𝐈𝐍𝐃: Locates a substring within a text string (case-sensitive).

𝐒𝐔𝐁𝐒𝐓𝐈𝐓𝐔𝐓𝐄: Replaces existing text with new text in a string.

𝐑𝐄𝐏𝐋𝐀𝐂𝐄: Replaces part of a text string based on the number of characters.

𝐋𝐄𝐅𝐓, 𝐑𝐈𝐆𝐇𝐓, 𝐌𝐈𝐃: Extracts characters from the left, right, or middle of a text string.

𝐓𝐄𝐗𝐓𝐉𝐎𝐈𝐍: Combines text from multiple ranges with a delimiter.

2. 𝐍𝐮𝐦𝐞𝐫𝐢𝐜 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬

𝐒𝐔𝐌, 𝐒𝐔𝐌𝐈𝐅, 𝐒𝐔𝐌𝐈𝐅𝐒: Adds numbers with conditions.

𝐀𝐕𝐄𝐑𝐀𝐆𝐄, 𝐀𝐕𝐄𝐑𝐀𝐆𝐄𝐈𝐅, 𝐀𝐕𝐄𝐑𝐀𝐆𝐄𝐈𝐅𝐒: Calculates the mean of numbers with conditions.

𝐌𝐈𝐍, 𝐌𝐀𝐗: Returns the minimum or maximum value in a range.

𝐑𝐎𝐔𝐍𝐃, 𝐑𝐎𝐔𝐍𝐃𝐔𝐏, 𝐑𝐎𝐔𝐍𝐃𝐃𝐎𝐖𝐍: Rounds numbers to a specified number of digits.

𝐀𝐁𝐒: Returns the absolute value of a number.

𝐌𝐎𝐃: Returns the remainder after a number is divided by a divisor.

3. 𝐃𝐚𝐭𝐞 𝐚𝐧𝐝 𝐓𝐢𝐦𝐞 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬

𝐓𝐎𝐃𝐀𝐘: Returns the current date.

𝐍𝐎𝐖: Returns the current date and time.

𝐃𝐀𝐓𝐄: Returns the date from year, month, and day.

𝐄𝐃𝐀𝐓𝐄: Adds a specified number of months to a date.

𝐍𝐄𝐓𝐖𝐎𝐑𝐊𝐃𝐀𝐘𝐒: Returns the number of working days between two dates.

𝐓𝐄𝐗𝐓: Formats date/time to text.

𝐘𝐄𝐀𝐑, 𝐌𝐎𝐍𝐓𝐇, 𝐃𝐀𝐘: Extracts the year, month, or day from a date.

𝐇𝐎𝐔𝐑, 𝐌𝐈𝐍𝐔𝐓𝐄, 𝐒𝐄𝐂𝐎𝐍𝐃: Extracts the hour, minute, or second from a time value.

4. 𝐋𝐨𝐨𝐤𝐮𝐩 & 𝐑𝐞𝐟𝐞𝐫𝐞𝐧𝐜𝐞 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬

𝐕𝐋𝐎𝐎𝐊𝐔𝐏: Looks for a value in the first column and returns a value in the same row.

𝐈𝐍𝐃𝐄𝐗 𝐌𝐀𝐓𝐂𝐇: Combines INDEX and MATCH for more flexible lookups.

𝐗𝐋𝐎𝐎𝐊𝐔𝐏: A more advanced and flexible lookup function than VLOOKUP.

𝐋𝐎𝐎𝐊𝐔𝐏: Looks up a value in a one-row or one-column range.

5. 𝐋𝐨𝐠𝐢𝐜𝐚𝐥 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬

𝐈𝐅: Performs a logical test and returns one value for TRUE and another for FALSE.

𝐈𝐅𝐄𝐑𝐑𝐎𝐑/𝐈𝐅𝐁𝐋𝐀𝐍𝐊/𝐈𝐅𝐍𝐀: Checks for error/blank/na value

𝐀𝐍𝐃, 𝐎𝐑, 𝐍𝐎𝐓: Combines multiple logical tests.

Mastering these functions will not only make you more efficient but also set you apart in any Data Analyst interview!