The INDIRECT function returns the reference specified by a text string. The INDIRECT function syntax is as follows: = INDIRECT ( reference_text ; reference_style ) Reference text can be any text that the function can “convert” to a cell reference. Reference style represents the style of referencing cells in Excel. If omitted or TRUE, reference text is interpreted as an A1 style reference i.e. the default style of referencing cells in Excel. If FALSE, reference text is interpreted as an …
Author: administrator@dailyexcel.net
CSV files in Excel: How to convert a comma-delimited file to a semicolon-delimited file, and vice versa
A CSV file is a plain text file used to store and transfer data between different, often incompatible, applications and systems. In these plain text CSV files, each line of text is a data record. Also, each line of text (i.e. each data record) consists of one or more fields, separated by delimiters. Those delimiters are originally commas [,], hence the name comma-separated values (CSV). However, data can in principle be delimited with a variety of special characters. Since comas …
How to MATCH numbers formatted as text in Excel
You will sometimes encounter errors while trying to MATCH or LOOKUP data in Excel if your numbers are formatted as numbers in one of your tables, and as text in another table. While special formats are available in Excel, they are relatively rarely used and are limited. Some “numbers” such as identification numbers are more often stored as text. This is done in order to add leading zeros, hyphens and other characters to that numbers. However, if we try to …
Top 10 lists in Excel
When analyzing large amounts of data in Excel, the best approach often is to retrieve top (10) values. Consider the following example: Here, we have a table with the invoices from the 29th of January. There are 904 invoices for that day. Values in the invoice column are unique as those are invoice numbers. Values in the user column are not unique, as some users have multiple invoices on that day, and values in the team column are not unique …
RANK functions
Ranking is data transformation in which values are replaced by their rank when the data is sorted. For any two items, the first is either ranked higher than ranked lower than ranked equal to the second. Values can be ranked from the smallest, or from the largest. Ordinal ranking presumes that all items will receive distinct ordinal numbers, including items that compare equal. Distinct ordinal numbers for items that are equal can be assigned arbitrarily, but it is also …
SMALL & LARGE functions
The SMALL function returns the k-th smallest number from an array of numbers. The SMALL function syntax is as follows: = SMALL ( array; k ) Array can be any row, column, or a combination of both. k is an integer number representing the position of a number in the array if data in that array would be sorted from the smallest. Consider the following example: If k is 1, the smallest number will be returned. If k is …
Nesting formulas, functions and conditional statements in the SUMIFS criteria
We’ve previously established what are the rules of writing criteria in the SUMIFS function: we can reference any cell we can enter any number directly in the function we can enter text directly in the function, nested inside of quotation marks [“”] logical test is nested inside of quotation marks [“”], with comparison operator coming first and a number second. The story however does not end there, and advanced users can do even more with the SUMIFS function. We …
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 …
Combining SUMIFS with INDEX MATCH
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 …