Excel Flashcards
Can a Data Analyst Highlight Cells Containing Negative Values in an Excel Sheet?
Yes, it is possible to highlight cells with negative values in Excel using Conditional Formatting.
In Microsoft Excel, a numeric value can be treated as a text value if it precedes with what?
Apostrophe (‘).
What is the difference between COUNT, COUNTA, COUNTBLANK, and COUNTIF in Excel?
COUNT function returns the count of numeric cells in a range
COUNTA function counts the non-blank cells in a range
COUNTBLANK function gives the count of blank cells in a range
COUNTIF function returns the count of values by checking a given condition
What is the function to find the day of the week for a particular date value?
WEEKDAY()
How does the AND() function work in Excel?
AND() is a logical function that checks multiple conditions and returns TRUE or FALSE based on whether the conditions are met.
What is VLOOKUP and what are its limitations?
VLOOKUP is used when you need to find things in a table or a range by row.
Limitations:
1. It only looks at the columns to the right of the lookup value
2. Only finds the 1st match
3. The default setting is the approximate match (you can set the 4th argument to 0 or FALSE to find the exact match)
4. It isn’t case sensitive
5. Inserting a column anywhere in the lookup table will give incorrect results
What function would you use to get the current date and time in Excel?
TODAY() and NOW().
What is Pivot Table?
A PivotTable is an interactive way to quickly summarize large amounts of data.
A PivotTable is especially designed for:
1. Querying large amounts of data in many user-friendly ways.
2. Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas
3. Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you
4. Moving rows to columns or columns to rows (or “pivoting”) to see different summaries of the source data
5. Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information you want
What are INDEX and MATCH functions, and how do they work together?
INDEX retrieves the value at a given location in a range
When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you’ll need to supply both the row and column number.
MATCH finds the position of an item in a range
It isn’t case sensitive and it doesn’t care if the range is horizontal or vertical.
How they work together:
1. INDEX needs numeric positions.
2. MATCH finds those positions.
3. MATCH is nested inside INDEX.
What is the difference between a function and a formula?
Formulas in Excel are used to perform any calculation with the values or data present in cells written by the user, they don’t have a particular syntax, and can’t be used as functions.
A function is a pre-existing Formula in Excel, they do have a syntax, and they can be used as formulas.
