TOP

- Using VLOOKUP function for merge spreadsheets
*One of the most powerful and useful function in Excel - VLOOKUP, it allows to find required values in the table. About correct of its using - simply and clearly.* - Improved VLOOKUP function (VLOOKUP2)
*Embeded function VLOOKUP - one of the most powerful function in Excel. But it have the one significant disadvantage - it finds only the first entry of required value in the table and only at the far left column. But if need to find the 2-nd, 3-rd and not at the far left one?* - VLOOKUP for combination by two condition (VLOOKUP3)
*Improve variant of VLOOKUP - VLOOKUP3, which give us possibility to substitute values by matching two conditions. The function can be useful in cases, when we don't have unique values in a range by which we wish to do combination.* - Quick merging of big spreadsheets (VLOOKUP2D)
*How quicklymerge two big tables (using INDEX and MATCH functions).* - Creating reports via Pivot Tables
*How in a few seconds to do compact, correct and clear report from a huge table with thousands rows.* - Spreadsheet redesigner
*Preparing dataset for Pivot Tables.* - Power Pivot – tool for enhancing opportunities of Pivot Tables
*PowerPivot Add for Excel 2010, which allow create Pivot Tables from different data source and accelerate proccesing files with a large number of records.* - Sorting of worksheets in a workbook
*How quickly sort the sheets alphabetically in a big workbook* - Text concatenating from different cells (CONCATENATE)
*Text concatenating from different cells in Excel.* - Split text to several columns
*How quickly split a column with text to several columns.* - Merging of two columns with data
*How quickly merge data from two mutually non-overlapping columns with blank cells. Without any formulas, macros, etc. - only using mouse.* - Quick jump between worksheets
*How does quickly jump into required sheet in a workbook, if we have many sheets.* - Hotkeys in Excel
*Convenient table with hotkeys in Excel.* - Drop-down list in the cell
*How to make drop-down list in Excel in order not to input repeatedly the same data from the keyboard.* - Filling in blank cells in the list
*How quickly fill in blank cells in the column, inserting there values from higher cells.* - Text concatenation by the condition (CONCATIF)
*How to concatenate a text from the different cells by the condition, easy and fast.* - Removing password protection in Excel
*How quickly to find out a password for protected workshees in Excel.* - Worksheet collection
*How to collect the different workbooks into one file.* - Fast input today's date
*Two quick ways to input today's date.* - Autofill of cells
*How to automatic fill in the cells.* - Input fractions in Excel
*How to input ordinary fractions in Excel.* - Alignment autoshapes
*How to align the autoshapes on the edges of cells.* - How to do an indent while entering text to the cell
*Make an indent while entering text to the cell in Excel.* - Changing the direction of data entry
*Changing the direction of data entry, when after pressing Enter, cursor move not to down, but to any other direction.* - Cell with secret
*How to hide the cell content without sheet protection.* - Hint during filling in data into cell
*Hint during filling in data into cell.* - How to insert macros in Excel
*How to insert macros in Excel.* - How to hide a formula in the cell
*How to hide a formula in the cell from prying eyes.* - Duplicates removing
*How to remove duplicates in Excel?* - Duplicates finding
*How to find duplicates in Excel?* - Automatic text splitting by specified delimiter (SPLITUP)
*Function for excluding part of text by the specified separator.* - Cells merging without loss of text
*How to merge cells together with their contents.* - Maximal value by condition (MAXIF)
*Searching for maximal value by condition.* - Special copying values and formulas
*Copying the value which gives out the formula. Copying once many formulas without shift links and ranges.* - Separation of the text into parts
*Separation of the text from the right, left and inside.* - SQL query in Excel VBA
*Write an SQL query in Excel using VBA* - Import data from Access to Excel
*Import data from Access to Excel with a refresh ability*

- 1. What is VBA, basic concepts.
- 2. Code Debugger
- 3. Working with worksheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties
- 6.1. Data types (Variables)
- 6.2. Data types (Continued)
- 7.1. Conditions
- 7.2. Conditions (Continued)
- 8.1. Loops
- 8.2. Loops (Continued)
- 9. Procedures and functions
- 10. Dialog boxes (Dialog boxes)
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls
- 12.3. Controls (Continued)
- 12.4. Controls (Exercises)
- 13.1. Arrays
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. What is SQL? SQL essentials.
- 2. Select data from a database (SELECT)
- 3. Sorting the result records (ORDER BY)
- 4. Filter records (WHERE)
- 5. Using wildcards (LIKE)
- 6. Computed (calculated) columns
- 7. Aggregate functions (MIN, MAX, AVG etc.)
- 8. Grouping of the resulting set (GROUP BY)
- 9. Subqueries
- 10. Combining records from two tables (INNER JOIN)
- 11. Outer join (LEFT, RIGHT, FULL JOIN)
- 12. Combining the resulting sets (UNION)
- 13. Insert new records (INSERT INTO)
- 14. Create a new table (CREATE TABLE)