excel Flashcards
- What are the most important data formats seen in Excel, and how are they used?
The different data types that can be used in excel include:
1) Numbers
2) Dates: Can be displayed like MM/DD/YYYY, they can be added or subtracted.
3) Percentages: Like 0.08
4) Strings: text in excel is stored as a string
- What is the order of operations used when evaluating formulas in Excel?
Excel uses an order of operations when evaluating a formula in the formula bar:
Parentheses, exponents, multiplication, division, addition, subtraction.
- What is a function in Excel?
A function is a predefined formula that performs calculations using specific values in a particular order. For example sum, average or even vlookup
- What is the VLOOKUP function?
VLOOKUP is designed to retrieve data in a table organized into vertical rows, where each row represents a new record. They’re used to look up values from an external table.
- What is the syntax of the VLOOKUP function and what’s a disadvantage?
The syntax of V(vertical)LOOKUP is:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
To use VLOOKUP, you supply 4 pieces of information, or “arguments”:
1) The value you are looking for (lookup_value)
2) The range of cells that make up the table (table_array)
3) The number of the column from which to retrieve a result (column_index)
4) The match mode (range_lookup, TRUE = approximate, FALSE = exact)
The column_index is entered manually so if the more columns are added after the function is written it’ll return the wrong the data.
Index match has similar functionality but doesn’t have that disadvantage
- What’s the index function ?
The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.
SYNTAX:
=INDEX (array, row_num, [col_num], [area_num])
- What’s the match function?
The MATCH function is designed for one purpose: find the numeric position of an item in a list. For example, we can use MATCH to get the position of the word “peach” in this list of fruits like this:
=MATCH(“peach”,B3:B9,0)
1) the value you want to look up
2) array or range of cells
3) 1,0, or -1. Approximate, exact, approximate
This is will return the position in the column that it is
- How to use MATCH and INDEX functions together
=INDEX(C3:E11,MATCH(H2,B3:B11,0),2)
The match will return the row number
- What is the difference between absolute and relative cell references? PART 1
As many Excel users know, one of the most magical features of Excel is the cell reference. Cell references allow users to include the values of external cells in formulas dynamically — rather than hard-coding particular values manually.
However, cell references can be confusing when copied and pasted to different locations. By default, Excel uses relative cell references, which change dynamically as they are copied and pasted around a sheet. For example, if a reference to cell A1 is copied and pasted one row down and one column to the right, the new reference will point to cell B2. This allows users to perform similar calculations on different ranges of cells quickly and easily.
- What is the difference between absolute and relative cell references?In which situations would you use each? PART2
In contrast, absolute cell references do not change when they are copied and pasted to other locations within a sheet. Absolute cell references can be used on either rows, columns, or both at the same time, and are indicated using the $ sign. For example, if a reference to cell $A$1 is copied and pasted one row down and one column to the right, the new reference will point to cell A1 — it won’t change at all, because both the row and column are locked. If a reference to cell $A1 is copied and pasted one row down and one column to the right, the new reference will point to cell A2 — only the row number will change, because the column letter is locked.
Here’s a handy table that will show you what the $ sign means depending on where you see it in a cell reference:
Format Meaning Explanation
$A$1 Row and column locked Cell reference will not change at all as cell is copied and pasted.
$A1 Column locked Only row reference will change as cell is copied and pasted.
A$1 Row locked Only column reference will change as cell is copied and pasted.
A1 Nothing locked Both row and column will change as cell is copied and pasted.
11.What is a PivotTable, and when would you use one?
As one of the most-used Excel features in business settings, PivotTables are sure to come up during any in-depth Excel interview. Be sure you’re prepared in advance with a firm grasp of what exactly PivotTables are, and why they’re useful in practice.
Simply put, a PivotTable is a tool used to summarize large quantities of data quickly and easily. It can help you analyze a data set of tens, hundreds, or even thousands of rows with minimal effort using a number of pre-defined functions — like SUM, COUNT, and AVERAGE.
There are many use cases for PivotTables, but they’re most handy when you need to analyze a large data set quickly. If you’ve got high-level, one-off questions on a massive data set — for example, “how many cookies did we sell in February of last year”, or “which salesperson closed the most deals this March”, chances are a PivotTable is the perfect way to answer them.
.
12.What are the key PivotTable ‘sections’ into which users can drag columns?
Each PivotTable is composed of a number of key sections, into which the columns of a target data set can be bucketed:
- Report filter: This section allows us to filter our table by one or more criteria. For example, we can only show data in our Pivot Table for the month of January.
- Column labels: This section allows us to summarize data across columns, placing data labels along the top of the screen.
- Row labels: This section allows us to summarize data across rows, placing data labels along the side of the screen.
- Values: This section allows us to specify what we’re summarizing — for example, total sales or number of items ordered.
- Do PivotTables have any drawbacks? How can they be solved?
Of course, no Excel feature is without its drawbacks, and there’s a chance your interviewer will dive deeper into your PivotTable knowledge by asking you to explore some of their weaknesses. This will help the recruiter assess your in-depth experience on one of Excel’s most important features — after all, PivotTables can’t be used for everything!
If asked about the drawbacks of PivotTables, consider the following:
Input data needs to be formatted properly. PivotTables can only be used in specific situations in which the input data set appears in flat file format — meaning that it’s broken down to it’s most granular level. If data is already summarized on a table, PivotTables may not be the best way to analyze it.
PivotTables need to be refreshed if input data changes. This can lead to confusing and errors when using PivotTables as part of larger dashboards.
PivotTables are easily modified, so it can be difficult to recreate your calculations. There are many times during which you’ll arrive at an answer using PivotTables, then have a difficult time recreating that answer if a supervisor asks to see your work in more detail. The flexibility of PivotTables can be a double-edged sword!
As an alternative to PivotTables, consider using conditional summary functions like SUMIFS and COUNTIFS, particularly when constructing dashboards. They can produce similar results, but are less ‘fluid’ — making your results more predictable and easier to track.
