Using conditional formatting to highlight dates

CF_Dates_4

Date functions in Excel make it is possible to perform date calculations, like addition or subtraction, resulting in automated or semi-automated worksheets. The NOW function, which calculates values based on the current date and time, is a great example of this. Taking this functionality a step further, when you mix date functions with conditional formatting, …

Continue reading »

Calculation without equal

Calculator_01

  I always wonder why, in most of the offices, there is a calculator close to a computer. It’s totally ridiculous especially if you have Excel on your computer. But a secretary told me that it’s not convenient for her to start with the calculation with the symbol =     In fact, there is a …

Continue reading »

Create an Offset Vlookup

offset_lookup_2

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 be solved We have a table (column A:D ) that lists the sales …

Continue reading »

Extract without duplicates with formula

Remove_Duplicate_Formula_1

It is very easy to extract without duplicates data from a table through the menu Data>Remove duplicates. It works great for one or more columns but the thing about this tool is that you can not control data that will be delete. I recently worked on a customer address file by keeping only the most …

Continue reading »

Create an online survey with Excel

Survey_3

Since it is possible to use Excel from a web browser, new special features are available with Excel Online. This is particularly the case for creating, posting and collecting the results of a questionnaire. And not only is it very easy to implement a surver but it is free. Click on this link to see …

Continue reading »

How to round in k$ without formula?

Round_K$_1

In many companies, it is difficult to present in a worksheet, big numbers. So, to avoid that, it is better to present  your figure rounded in kilo dollar (k$) or million (m$). But how to round in k$? In this article you will find 3 techniques to display a result in k$ (or m$ for the millions]. Each …

Continue reading »

Major update

Recently Microsoft has announced a new public update to Excel 2013. This new release includes major stability and usability improvements in Self Service BI scenarios – especially when Power Query, Power Pivot and Power View are involved. For this one we eliminated over 120 bugs, crashes and hangs. On the other hand, we introduced two new …

Continue reading »

How to Lookup Values to left column?

Function_INDEX_MATCH_1

The VLOOKUP function searches for data in a table by returning data always to the right of the column containing your ID. However, it is sometimes necessary to us to return the value that is located into the left columns of your ID. As the VLOOKUP function is not able to do, we can solve the problem …

Continue reading »

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 …

Continue reading »

Older posts «