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!
