Category: Excel Tips & Tricks

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 …

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 …

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 …

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 …

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 …

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 …

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 …

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 …