Excel Tables

What are Excel Tables? Excel Tables, or just Tables, are tables superimposed over regular cells. Excel Tables are assumed to be containing structured data. Both tables and individual table columns are named and intended to be referenced in formulas by those names. As they are typically used to load structured data from an external source into Excel, and that external data is typically accessed from Excel using the Data ribbon and then loaded into Table, they are also sometimes called

Unbreakable links in Excel

Links, i.e., external references, are used in order to retrieve data from an external source, usually another workbook. Only retrieved values and addresses for those values will be saved in the current workbook. Addresses are saved in order to enable updates, i.e., when the source changes, or the source is changed, so does the current workbook. However, if we want to disable future updates to the current workbook, or if we want to forward our workbook to an external user

Cleaning up text in Excel: CLEAN, TRIM, and SUBSTITUTE

In Excel, you will occasionally deal with text fields containing line breaks, multiple spaces, hyphenation, currency and letterlike symbols, etc. This is common when dealing with unrestricted user-inputted data and can cause issues. In particular, these kinds of values will be hard to connect with related values without cleanup. When we MATCH those values with related data tables, errors are bound to occur.   Basic cleanup approaches include manual replacement of all of the unnecessary characters with a desired or

EOMONTH function – calculate past or future date from the starting date

You will sometimes need to calculate one date based on another date. Most often, you will need the first or last date of the current month, the first or last date of the current year, the previous or future month, year, quarter dates etc. No matter what kind of dates you need, the simplest way to generate them will include EOMONTH function.   The EOMONTH function returns the last day of the month in relation to the start date.  

SUMIFS by text string length

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 far less common need – but still crucial in specific situations.   We can accomplish this task

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

Creation of PowerPoint presentations, and especially updates of recurring PowerPoint presentations, can easily become tedious process prone to errors. This is especially true with presentations filled with (tabular) data and charts. Linking of 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 in a more suitable tool than PowerPoint, Excel. Both values and also structure of the slides can be modified without modifying

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 ourself in any cell that is a part of a table and use the Filter button in order to insert a filter:   This is how a

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 column and row part 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 or