Using IF with AND, OR, and NOT functions in Excel
Here are overviews of how to structure AND, OR and NOT functions individually. When you combine each one of them with an IF statement, they read like this: Following are examples of some common nested IF (AND ()), IF (OR ()) and IF (NOT ()) statements in Excel.
Look up values with VLOOKUP, INDEX, or MATCH - Microsoft …
It's actually quite easy to do with a lookup function. The VLOOKUP and HLOOKUP functions, together with INDEX and MATCH, are some of the most useful functions in Excel. Note: The Lookup Wizard feature is no longer available in Excel. Here's an example of how to use VLOOKUP. =VLOOKUP (B2,C2:E7,3,TRUE)
How to compare data in two columns to find duplicates in Excel
You can use the following methods to compare data in two Microsoft Excel worksheet columns and find duplicate entries.
IF function – nested formulas and avoiding pitfalls
The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
Create a parameter query (Power Query) - Microsoft Support
Create a parameter query (Power Query) You may be quite familiar with parameter queries with their use in SQL or Microsoft Query. However Power Query parameters have key differences: Parameters can be used in any query step. In addition to functioning as a data filter, parameters can be used to specify such things as a file path or a server name.
Sum values based on multiple conditions - Microsoft Support
Let's say that you need to sum values with more than one condition, such as the sum of product sales in a specific region. This is a good case for using the SUMIFS function in a formula. Have a look at this example in which we have two conditions: we want the sum of Meat sales (from column C) in the South region (from column A).
FILTER function - Microsoft Support
The FILTER function allows you to filter a range of data based on criteria you define. In the following example we used the formula =FILTER (A5:D20,C5:C20=H2,"") to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string ("").
Automate tasks with the Macro Recorder - Microsoft Support
To automate a repetitive task, you can record a macro with the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply a single format to all of them. A macro can do that for you. You can record a macro applying the format you want, and then replay the macro whenever needed.
Undo, redo, or repeat an action - Microsoft Support
You can undo, redo, or repeat many actions in Microsoft Word, PowerPoint, and Excel. You can undo changes, even after you have saved, and then save again, as long as you are within the undo limits (By default Office saves the last 100 undoable actions). To undo an action press Ctrl+Z on your keyboard, or select Undo on the Quick Access Toolbar.
Using structured references with Excel tables - Microsoft Support
When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. Here's an example of what Excel does: That combination of table and …
Merge and unmerge cells - Microsoft Support
You can't split an individual cell, but you can make it appear as if a cell has been split by merging the cells above it. Select the cells to merge. Select Merge & Center, then Merge Cells.