TOP
- Join Tables (VLOOKUP)One of the most useful functions in Excel - VLOOKUP, allows you to combine tables by a given parameter. About its correct use - simple and clear.
- Improved function VLOOKUP (VLOOKUP2)The built-in function VLOOKUP is one of the most powerful functions in Excel. But it has one significant drawback - it finds only the first occurrence of the desired value in the table and only in the far right column. But if you need the 2nd, 3rd and not the last one?
- VLOOKUP for combination according to two conditions (VLOOKUP3)An improved version of the VLOOKUP function is VLOOKUP3, which gives us the opportunity to substitute values when two conditions match. This function can be useful in cases where we do not have unique values in the field on which we need to perform a combination.
- Quick merge of large tables (VLOOKUP2D)How to quickly combine two large tables together with columns and rows, that is, make a selection not by one parameter (as the functions VLOOKUP or HLOOKUP), but by two at once (using the functions INDEX and MATCH).
- Creating reports using PivotTables (PivotTables)How to create a compact, visual and correct report from a huge table with several thousand rows in a few seconds?
- Add-on for expanding the capabilities of Pivot Tables (PowerPivot)Add-on for Excel 2010, which allows you to create PivotTables from various data sources and speed up the processing of files with a large number of records.
- Selective cell summation (SUMIFS)How to selectively summarize values in a table that meet multiple conditions at the same time.
- Concatenation of text by condition (CONCATIF)How to connect text from different cells according to a given condition, simply and quickly.
- Unpivottables (Unpivot)Preparation of data set for Pivot Tables.
- Removing a password in Excel (PasswordRemover)How to quickly remove protection from a sheet or find out the passwords for protected sheets of the book in the Excel file.
- Combining two columns with dataHow to quickly combine data from two columns with empty cells that do not overlap each other. Without any formulas, macros, etc. - just using the mouse.
- Quick transition between pages of the bookHow to quickly go to the necessary sheet of the book if we have many sheets?
- Combine Workbooks (CombineWorkbooks)How to collect different books in one file?
- Sorting sheets in a book (SortSheets)How to quickly sort sheets alphabetically in a large book?
- Filling empty cells in the listHow to quickly fill empty cells in a column by inserting values from previous cells.
- Concatenation of text from different cells (CONCATENATE)Connecting (gluing) text from different cells in Excel.
- Automatic splitting of one data column into severalHow to quickly and automatically split a column with data (for example, with full name together) into three separate columns with last name, first name, and patronymic.
- Convenient table with "quick" keys in Excel.
- Quick entry of today's date (TODAY)Two quick ways to enter today's date.
- A drop-down list in a cellHow to create a drop-down list in Excel, in order not to re-enter the same values from the keyboard.
- A drop-down tooltip when filling in data in a columnA drop-down tooltip with options for already entered values when filling in data in a column.
- Linked drop-down listsCreation of linked drop-down lists in a cell, when, depending on the selected value in one list, the set of values in the other is automatically changed.
- Sum, number, numbers in writing in Excel (SUMINWORDS)Custom function for displaying sums, numbers and digits in Ukrainian. For example, the amount of 10,568.23 should be displayed as Ten thousand five hundred and sixty-eight UAH. 23 kopecks Or we display the number "27" as "Twenty-seven".
- Transliteration from Ukrainian to English (TRANSLIT)Custom function for displaying Ukrainian text in Latin ("Petro" in "Petro").
- Printing forms Word with data from ExcelHow to automatically print a large number of Word forms of the same type, which must be filled with data (names, addresses, companies) from the Excel table.
- Interactive diagramHow to create a "live" interactive chart with scrolling in time, zooming of individual fragments and turning on and off the desired rows using flags.
- Entering fractionsHow to enter ordinary fractions in Excel.
- Alignment of autoshapesHow to align autoshapes on cell edges.
- Checking the correctness of entered dataPreliminary check of the correctness of entering data into the cell.
- A cell with a secretWe hide the contents of the cell without protecting the sheet.
- Extracting part of the characters from the text (LEFT, RIGHT, MID)Highlight part of the text on the right, left and inside.
- Special copying of values and formulasCopy the values produced by the formula. Copying many formulas at once without shifting references and ranges.
- Maximum value by condition (MAXIF)Finding the maximum value by condition.
- Merge cells without losing text (MergeCell)How to combine cells together with their contents.
- How to insert macro in Excel?Add the macro code to Excel.
- Import of NBU exchange rates in Excel (NBU_RATE)A function that allows you to automatically insert the official exchange rate of the NBU into Excel for any date. Data are obtained from the NBU website.
- Automatic splitting of text by the given separator (SPLITUP)Function for extracting the necessary fragment from the text by the given separator.
- Search for duplicatesHow to find duplicates in Excel?
- Duplicate removalHow to remove duplicates in Excel?
- Changing the interface languageQuickly change the language of the interface and help in Excel without a new installation of Office.
- Autofill cellsAutomatic filling of cells in Excel.
- Working hoursCustom function to count working hours between two dates in Excel.
- Filtering unique valuesHow to quickly and easily filter unique values in a large list.
- How to hide a formula in a cellHow to hide the formula in a cell from prying eyes.
- How to protect a sheet or workbook in ExcelProtecting the contents of a letter or workbook in Excel from third-party users.
- How to change column namesChanging the name of the columns in Excel to the standard view.
- Add a macro button to the toolbarAdd a macro button to the toolbar.
- SQL query in Excel VBA (SQLQuery)We write an SQL query in Excel using VBA.
- Creating graphs (diagrams) in ExcelWe create a graph in Excel simply and quickly.
- How to add a second axis to a graphAdd the second data axis to the Excel chart.
- We change the direction of data entryWe change the direction of data entry, when after pressing Enter, the cursor moves not down, but in any other direction.
- Import data from Access to ExcelImport data into Excel from an Access database.
- How to create a paragraph when entering text into a cell?We create a paragraph when entering text into the Excel cell.
- We perform division in cells using the macro (Division)Macro for automatic division of values in cells Excel. It can also be used to convert values into other currencies.
- Function OFFSETWe explain how the OFFSET function works.
- Amount, number, numbers written in WordHow to automatically display the sum in words in Word.
- A spider macro for collecting data from various filesSearch and collect information from different workbooks into one file.
- Calculation of the number of unique valuesCounting unique values in table Excel.
- Search for Latin letters in Cyrillic text and vice versa (ShowLatin, ShowCyrylic)Custom function for searching Latin letters in Cyrillic text and vice versa.