Excessively large files in Excel: How to remove blank cells

In order to limit file sizes, Excel typically doesn’t store information about the whole spreadsheet (all available 1048476 rows and XFD columns); only the part of the worksheet that contains information is stored. However, “empty” cells may contain leftover formatting or comments, thus forcing Excel to save parts of the spreadsheet that contain no useful information. This can cause excessively or even extremely large file sizes, occasionally combined with printing and calculation time issues. In order to restore proper functionality

Custom Date Formats in Excel

In addition to the preset options available for formatting dates in Excel, for specific use cases, additional custom date formats can also be created (coded). All rules for the creation of custom number formats apply. Custom date formats are created and stored only in the active workbook, and they affect only the way a number is displayed and do not affect the underlying value of the number.   Pre-defined formats for date and time in Excel are available under the

Format Numbers as Thousands, Millions, or Billions in Excel

You will sometimes need to report some of your figures in thousands, millions, or billions. This is typically accomplished by dividing the original figures by thousands, millions, or billions, and can be time and/or resource consuming process. However, Excel also offers an alternative in custom number formats. In Excel, numbers can be formatted to be shown as thousands, millions, or billions without any sort of calculations or modifications to the underlying numbers.   Custom number formats are created (modified from

Custom Number Formats in Excel

In addition to the preset options available for formatting numbers in Excel (decimal number, currency, percentage, date, etc.), for specific use cases, additional custom number formats can also be created (coded). Custom number formats are created and stored in the active workbook and are not available to outside workbooks. As with other formatting options, (custom) number formats affect only the way a number is displayed and do not affect the underlying value of the number.   Consider the accounting number

Invisible Numbers with Conditional Formatting

You will sometimes need to make some of your numbers appear invisible. In this text we will show how to accomplish this goal using a custom number format, as we want to avoid the commonly used white font color and other problematic methods of accomplishing this goal.   Consider the following example: This is a table that will be linked into a PowerPoint presentation, and the gridlines are not shown. Multiple cells contain zeros. We want those to appear invisible

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

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

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

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

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