How to customize the Quick Access Toolbar

The Quick Access Toolbar is a customizable toolbar independent of the tab on the ribbon that is currently displayed in Microsoft Excel. It is usually located in the title bar of the Excel window, but it can also be configured to display below the ribbon. It will look something like this on a new installation: You can see often used commands Save, Undo and Redo already on the toolbar. If you want to remove those, or more likely, add some


You will sometimes want to retrieve data from a table with values both in rows and columns and based on multiple (three or more criteria). There is, as usual, more than one way to do this. However, most of those ways require you to use something called array formulas – in essence, you will have to press Ctrl Shift Enter combination on your keyboard every time you want to calculate that formula. Combining SUMIFS with INDEX MATCH enables us to

Remove Duplicates feature

You will sometimes want to retrieve unique values from a data set – i.e., to find and remove duplicate values. While there are a few ways to do this, the most effective option is Excel built-in feature Remove Duplicates. Remove Duplicates feature removes duplicate rows from the selected columns. Duplicate rows are deleted in this process. The simplest example of this process is removal of duplicate values from a single column: In this example, we’ve started with the data in

Generating a series of dates with the EOMONTH function

You will sometimes need to generate a series of dates in Excel. Most often you will need month-end dates, but no matter what kind of dates you need, the simplest way to generate them will include the EOMONTH function. The EOMONTH function returns the last day of the month in relation to the start date.   The EOMONTH function syntax is as follows: = EOMONTH ( start_date; number of months ) Start date is any given date, such as 31.12.2020.

Custom Icons for Conditional Formatting

With conditional formatting, among other things, we can join indication icons to our numbers. While we can rearrange available icons as we wish, choice of available icons is fairly limited: In particular, while Green Arrow Up and Red Arrow Down are available, we don’t have available often requested Green Arrow Down and Red Arrow Up. We can expand our choice of icons with the help of the Wingdings fonts – a series of fonts that render characters as a variety

Lookup with unique identifiers

INDEX function The INDEX function returns the value from a range of cells based on row and column specified in the function arguments. We are simplifying here, the INDEX function can actually return values from one or more arrays – i.e. broader term for lists of data which includes cell ranges. But the most common use is with cell ranges, as that is the typical way of structuring data in Excel.   Syntax of the INDEX function is as follows:

Conditional calculations

IF functions for cell ranges In our previous section we’ve primarily dealt with testing conditions inside of a row, i.e. one or more cells in that row, and returning some kind of a result based on conditions in that row. While that is useful when we want to add something new to our rows, we will often have to deal with retrieving data based on criteria from whole ranges of cells, sometimes containing thousands of rows and columns. IF function

Conditional statements

IF function basics Conditional statements, conditional expressions, conditional processing, IF-THEN or IF-THEN-ELSE are all names for a programming concept where conditions are tested and actions are performed if conditions are met. If a certain condition is met, something is calculated. If that certain condition is not met, either nothing happens, or something else is calculated. In Excel, when conditions are met, something is TRUE. When conditions are not met, something is FALSE. TRUE and FALSE are logical values. They are

Date and Time in Excel

How are date and time stored and formatted? In our previous section, we’ve already established that, no matter how date and/or time is formatted and possibly edited, underlying values saved in cells are still decimal numbers. Let’s expand on that. Excel era starts on January 1, 1900. If we enter number 1 in the cell and format it as date, the date we will see is in fact January 1, 1900. Zero will provide for a nonexistent date of January

Numbers in Excel

How are numbers stored, formatted and calculated? When dealing with numbers in Excel we should be aware of a few things: numbers in Excel can be formatted in many ways: plain numbers, currency, percentage, date etc. no matter how they are formatted and possibly edited, underlying values saved in cells are still decimal numbers those decimal numbers that Excel uses and calculates with go up to 15 decimal places (and even with that, there are caveats) this is not changed