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 by a variety of special characters. Since commas …
Author: administrator@dailyexcel.net
How to MATCH numbers formatted as text in Excel
You will sometimes encounter issues 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 those numbers. However, if we try to …
Top 10 lists in Excel
When analyzing large amounts of data in Excel, often the best approach is to retrieve the top (10) values. Consider the following example: This table contains all of the invoices from January 29th. There are 904 invoices for that day. The values in the invoice column are unique, as those are invoice numbers. The values in the user column are not unique, as some users have multiple invoices on that day, and the values in the team column are not …
RANK functions
Ranking is a 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 to the largest, or from the largest to the smallest. Ordinal ranking presumes that all items will receive distinct ordinal numbers, including items that compare equally. Distinct ordinal numbers for items that are equal can …
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 …
SUMIFS criteria: formulas, functions and conditional statements
We’ve previously established the rules for writing criteria in the SUMIFS function: we can reference any cell we can enter any number directly into the function we can enter text directly in the function, nested inside quotation marks “” a logical test is nested inside of quotation marks “”, with the comparison operator coming first and a number second. We will further expand on the topic in this article. Formulas and functions can also be nested in SUMIFS criteria. If needed, …
How to customize the Quick Access Toolbar
In Microsoft Excel, the Quick Access Toolbar is a customizable toolbar independent of the tabs on the ribbon. 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 the often-used commands Save, Undo, and Redo already on the toolbar. If you want to remove those, or more likely, add some commands you often …
Combining SUMIFS with INDEX MATCH
Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. When SUMIFS is combined with INDEX MATCH, that sum range doesn’t have to be defined anymore; it is now rather specified in the function arguments. By combining SUMIFS with INDEX MATCH, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and …
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 the Excel built-in feature Remove Duplicates. The Remove Duplicates feature removes duplicate rows from the selected columns. Duplicate rows are deleted in this process. The simplest example of this process is the removal of duplicate values from a single column: In this example, we’ve started with …
Combine EOMONTH with SEQUENCE: generate a sequence of dates
You will sometimes need to generate a sequence 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 combined with the SEQUENCE 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 ) The start date is …