Values that meet one or more criteria can be added up with the SUMIFS function. The sum of values next to cells containing specific text, i.e., a sum based on text criteria, is possibly the most common use case. The sum of values next to cells containing (non) specific text of a defined length (the defined number of characters in a text string) is a far less common need, but still crucial in specific situations. We can accomplish this …
Author: administrator@dailyexcel.net
Line breaks in Excel
A line break is the termination of one line of text, and the beginning of the next line. If needed inside of a single Excel cell, line breaks can be achieved in two ways: the Wrap Text feature allows you to make text appear to be structured into multiple lines, manual line breaks can be entered by pressing Alt + Enter. Consider the following sentence: As of Unicode version 14.0, there are 144,697 characters with code points, covering 159 …
UNICODE and UNICHAR functions
The Unicode Standard is the primarily used scheme for internal processing and storage of text, with almost 150 thousand code points representing characters. The Unicode Standard is supported in Excel. However, some functions for text manipulation still support only ASCII or ANSI character sets. Examples of those include the CLEAN function and the TRIM function. The CODE function and the CHAR function used for character manipulation also fall into that category. Full character manipulation is available through the UNICODE function and …
Automated presentations: linking of Excel data to PowerPoint
The creation of PowerPoint presentations, and especially updates to recurring PowerPoint presentations, can easily become a tedious process prone to errors. This is especially true with presentations filled with tabular data and charts. Linking Excel data to a PowerPoint presentation can significantly speed up that process and completely eliminate errors. Tables, charts, and other presentation items can be worked on with a more suitable tool than PowerPoint, Excel. Both values and the structure of the slides can be modified without …
Sort & Filter in Excel
How to insert or remove a filter Excel AutoFilter features enable us to quickly sort and filter, i.e., to quickly order, select from based on a criterion, or analyze tabular data. Feature is available under both Home and Data ribbons, as Sort & Filter: We can insert a filter by positioning ourselves in any cell that is a part of a table and using the Filter button: This is what a table with an inserted filter looks …
Combining INDIRECT with ADDRESS
We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both the column and row parts of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function. The ADDRESS function returns a text string that represents the address of a particular cell. Row, column, type of reference (locked or absolute), reference style (A1 …
INDIRECT function
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, the reference text is interpreted as an A1 style reference, i.e., the default style of referencing cells in Excel. If FALSE, the reference text is interpreted …
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 by a variety of special characters. Since commas …
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 …