I would like to say that the best program I've ever used for analyzing my data is excel. Since the late 90's Excel was there for me and is still here today. I'm not saying I didn't try other programs like R Studio, Power Bi and Tableau but excel is still helping me on my day by day routine.
The list below will give you my top functions and use cases with Excel.
1. VLOOKUP
Syntax
VLOOKUP( value, table, index_number, [approximate_match] )
The Microsoft Excel IF function returns one value if the condition is TRUE, or another value if the condition is FALSE.
The IF function is a built-in function in Excel that is categorized as a Logical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the IF function can be entered as part of a formula in a cell of a worksheet.
Please read our IF-THEN-ELSE (VBA) page if you are looking for the VBA version of the IF statement as it has a very different syntax.
Syntax
IF( condition, [value_if_true], [value_if_false] )
Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition.
For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify. Another typical use of COUNTIF in Excel is for counting cells with a specific word or starting with a particular letter(s).
Syntax
COUNTIF(range, criteria)
Hotkeys
ctrl + 1 - open format cells menu
VLOOKUP( value, table, index_number, [approximate_match] )
2. CONCATENATE
The word concatenate is just another way of saying "to combine" or "to join together". The CONCATENATE function allows you to combine text from different cells into one cell. In our example, we can use it to combine the text in column A and column B to create a combined name in a new column.
Syntax
CONCATENATE(Text A, Text B)
The word concatenate is just another way of saying "to combine" or "to join together". The CONCATENATE function allows you to combine text from different cells into one cell. In our example, we can use it to combine the text in column A and column B to create a combined name in a new column.
Syntax
CONCATENATE(Text A, Text B)
The Microsoft Excel IF function returns one value if the condition is TRUE, or another value if the condition is FALSE.
The IF function is a built-in function in Excel that is categorized as a Logical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the IF function can be entered as part of a formula in a cell of a worksheet.
Please read our IF-THEN-ELSE (VBA) page if you are looking for the VBA version of the IF statement as it has a very different syntax.
Syntax
IF( condition, [value_if_true], [value_if_false] )
Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition.
For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify. Another typical use of COUNTIF in Excel is for counting cells with a specific word or starting with a particular letter(s).
Syntax
COUNTIF(range, criteria)
5. SUMIF
The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria (for example, is equal to 2000).
The SUMIF function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMIF function can be entered as part of a formula in a cell of a worksheet.
To add numbers in a range based on multiple criteria, try the SUMIFS function.
Syntax
SUMIF( range, criteria, [sum_range] )
The SUMIF function is a worksheet function that adds all numbers in a range of cells based on one criteria (for example, is equal to 2000).
The SUMIF function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMIF function can be entered as part of a formula in a cell of a worksheet.
To add numbers in a range based on multiple criteria, try the SUMIFS function.
Syntax
SUMIF( range, criteria, [sum_range] )
6. OR, AND
Use the OR function, one of the logical functions, to determine if any conditions in a test are TRUE.
Use the AND function, one of the logical functions, to determine if all conditions in a test are TRUE.
Syntax
OR(A2>1,A2<100)
AND(A2>1,A2<100)
7. Left, Right, Mid
LEFT returns the first character or characters in a text string, based on the number of characters you specify.
Syntax
LEFT(text, [num_chars])
Use the OR function, one of the logical functions, to determine if any conditions in a test are TRUE.
Use the AND function, one of the logical functions, to determine if all conditions in a test are TRUE.
Syntax
OR(A2>1,A2<100)
AND(A2>1,A2<100)
7. Left, Right, Mid
LEFT returns the first character or characters in a text string, based on the number of characters you specify.
Syntax
LEFT(text, [num_chars])
Pivot Tables
When it comes to Pivot table you know you are going to save time. No need to memorize all these functions as sumif and countif, you just drag and drop and the pivot will determine if to sum or count your values. The pivot table enables you to do things faster and in a nicer way.
You can also add slicers and other cool options to make your pivot be more easy to use.
Hotkeys
ctrl + 1 - open format cells menu
ctrl + shift + 1 - format number as currency (i.e 1000 will be formatted to 1,000.00)
ctrl + shit + ~ - format number as regular (i.e 1,000.00 will be formatted to 1000)
A good explaination can be found here.
Comments
Post a Comment