Excel
- Formulas and Functions:
- Functions:
- VLOOKUP(): Searches for Key in a table. After matching the Key, it returns the requested value in that record.
- INDEX():Basically this function receives a range of cells, a row index and a column index as input. It returns the the value or reference of the cell at the specified row and column index.
- COUNT(): Returns the number of cells in a range which have numeric values in them
- &, Concatenating Text: Concatenating text can be achieved by using the & character. Different methods in VBA can be used, including for a range.
- FIND(): The function FIND() searches for a specific text expression inside another text and returns the first index which the text was found
- MID(), LEFT(), RIGHT(): The main purpose of these functions is to return a certain number of characters (substring) from a text.
- IF(): Receives 3 input parameters. The first input parameter is a logical test. The logical test will either equate to True or False. If the logical test equates to TRUE then the second parameter will be returned If the logical test equates to False then the 3rd parameter will be returned
- LEN(): Returns the number of characters in a text string.
- TEXT(): Converts a number value to a text string, with the format specified.
- INDIRECT(): Returns a reference specified by a text string
- CHAR(): Receives an number as input and returns the character associated with it.
- DATE(): Creates a date value by receiving the year, month and day.
- DATEVALUE(): Creates a date value based on a date expression.
- MATCH(): Searches a 1 dimensional range for a target value.
- INDEX-MATCH: Explains how the two function INDEX and MATCH can be combined to create a functionality similar to VLOOKUP and HLOOKUP.
- Change Formula Calculation to Manual: Explains how you can change the formula calculation to manual in an excel workbook
- Functions:
- Tutorials:
- Create Invoice/Bill Using VLOOKUP (Step by Step Tutorial): A step by step tutorial on how to use VLOOKUP to create an invoice.
- Excel PivotTable: Explains the Excel PivotTable.
- Insert Commands:
- Excel PivotTable: Explains the Excel PivotTable.
- Hyperlinks To Cell or Range: Explains how to use hyperlinks to take the user to another location in the workbook.
- Samples:
- Excel Functions and Formulas Sample #1, Split Strings Based on Delimiter: In this article I will provide an example on how you could split a string based on a delimiter character using excel formulas and functions.
- Excel Functions and Formulas Tutorial / Sample Text and Strings Sample #2 (Advanced): A more advanced Example of using Excel’s functions and formulas.
- General:
- Dates: Explains the different methods for defining dates in excel.
- Security Warning: Automatic Update of Links Has Been Disabled: Explains how to overcome this security warning.
- Getting Data From Another Workbook Through Links
- Connect to External Data (Access Database): Explains how to connect to an access database and import data from a table.
- Getting Data From the Web: Explains how you can get data from websites .
- Excel 2010 Show Developer Ribbon
- Excel, Can’t Remove Cell Fill Color
- Access Create Link To Excel Sheet
- Custom Formats:
- Month Language: Explains how you can specify the language to display the month text string used in dates.
- Semicolon Character: Explains the semicolon character used in custom formats.
- @ Character: Explains the @ character used in custom formats.
- Date Formatting: Explains how to apply different formats for displaying dates.
- Conditions: Explains how to apply conditions to custom formats.
- Colors: Explains how to change the font color using custom formats.
- Removing Digits: Explains how to remove digits in custom formats.
- Adding Characters and Text: Explains how to add characters and text strings to values in cell using custom formats.
- 0, # and ? characters: Explains the characters “0”, “#” and “?” in custom formats.
- Formula Speed and Efficiency: Articles that compare the efficiency and the speed of the different formulas in excel
- IF (Formula vs VBA): Compares the speed of the “IF” function in excel, with an If statement in VBA for Excel.
- VLOOKUP vs INDEX and MATCH Speed Comparison: Compares the speed and efficiency of the VLOOKUP function, vs the combination of the INDEX and MATCH functions.