Category: Expert

Create an Offset Vlookup

In the case you have a table that contains the same ID for many – or empty cells. It is strongly recommended to reorder your data To perform this modification, we will use 3 functions , INDEX , MATCH and OFFSET. Problem to solve We have a table (column A:D ) with the list of sales …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/create-an-offset-vlookup/

Vlookup to the left in Excel

Problem of the VLOOKUP function The normal behavior of the VLOOKUP function is to return data from a table. But you can only return data on the right of the column containing the ID. However, in some cases, you can not change the order of the columns. Like for instance in this document where the …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/vlookup-to-the-left-in-excel/

How to make automatic calendar in Excel

Creating a new calendar each month is a waste of time, but many people do as they think they need to rebuild it to get correct weekends and public holidays. But in fact, with few formulas, 2 conditional formatting rules, 1 dropdown list and 5 lines of VBA code, you can create an automatic calendar. …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/how-to-make-automatic-calendar-in-excel/

Relationship between tables

 

Permanent link to this article: https://www.excel-exercise.com/relation-between-tables/

Conditional Formatting – Highlighting dates

With Date functions in Excel, it is possible to perform calculations like addition or subtraction and thus, produce automated or semi-automated worksheets (using the NOW function in particular). Date functions mix with conditional formatting, allow you to create spraedsheets displaying date alerts automatically when a deadline is near. Basics of Conditional formatting with dates In the menu Home …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/highlighting-dates/

Example of Date, Week, Gap in days

This article presents all cases to calculate automatically a date from another date. All formulas are based for example on the current date TODAY () but of course all formulas work with any other date. Number of days in a month Excel allows you to calculate number of days in a month. Here you have …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/example-of-date-week-gap-in-days/

Change the Themes

You can customize Excel 2007 or 2010 by modifying the basic colors as well as adding your own menus. Custom themes By default, colors and fonts are loaded into the Home menu / Police.     Ditto for the effects applied to the forms Now you can change themes by going to the Page Layout …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/change-the-themes/

Function INDEX in Excel

The INDEX function returns the contain of a cell at the intersection of a row and a column in a table or a reference. Presentation of the function INDEX The INDEX function works with three parameters: References of a range of cell containing the value to return The row index The column index [Optional] zone …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/function-index/

Pivot Table – Generate multi-worksheets

Pivot Table is a great tool to split a file into multiple worksheets with a simple action. Presentation of the tutorial If you are reading this article it’s because you have a huge table and your have to split the contain in many worksheets. Methodology To make this work, we must create a pivot table. To see the method, …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/generate-multi-worksheets/

Function INDIRECT

The INDIRECT function helps you to create dynamic references. In other words, you can replace a part of a reference by a variable. Presentation of the function INDIRECT Let’s suppose you have a workbook with a lot of worksheets. Each worksheet has the same structure with these informations. You want to create a worksheet that …

Continue reading

Permanent link to this article: https://www.excel-exercise.com/function-indirect/