Tag: Microsoft Excel

Microsoft Excel Security Warning Data Connections Have Been Disabled

by Greg Johnson, posted in Software

When using Microsoft Excel, upon opening a file, you may see the following message:

Security Warning: Data Connections Have Been Disabled

This warning message is disconcerting, annoying, and in most cases unnecessary.

While Excel can have active data connections with other files and cloud-based data sources, sometimes there can be old data connections that aren’t necessary.

Perhaps at one point you imported a CSV file into columns on an existing sheet. That import session may be saved under connections, even though there isn’t actually a dynamic real-time connection to the long-ago-deleted CSV file.

Follow these steps to remove unnecessary data connections from Excel files:

  1. Make a backup of your spreadsheet file.
  2. On the Data menu/ribbon click on Connections. A visual of this is below from Excel 2016 for Mac. See the Connections option under the Data tab.
    20150828fr0552-microsoft-excel-2016-apple-mac-data-connections
  3. You’ll see a list of current data connections as shown below. Remove any that aren’t in use. Keep in mind that this probably won’t have any negative impact, unless you’re using a more complex spreadsheet workbook (like at work) that actually uses active data connections. Notice in the example below, there are no locations in the workbook where the data connection is being used. So, it’s just unnecessary at this point.

20150828fr0552-microsoft-excel-2016-apple-mac-data-connections-ribbon

Microsoft Excel Style Colors Can Result in Lost Date Cell Formatting

by Greg Johnson, posted in Software, Tech Tips

Microsoft Excel has some nice pre-defined styles to easily and quickly highlight a group of cells, columns, or rows.

However, if you’re using these to mark your location in a sheet as you’re entering data or for some other temporary reason, a bug in Microsoft Excel 2016 (Apple Mac version) will cause the date formatting to be lost in any columns that were defined to be formatted as dates. So, the dates will be converted into a scientific notation equivalent number.

If this happens, you need to highlight the relevant column and then apply the cell formatting you’d prefer.

To avoid this problem, don’t use the predefined Excel styles. Instead, use the Fill color option found on the Home ribbon just to the right of bold/italic/underline. This can be applied and then removed without impacting the cell formatting.

Microsoft Excel 2016 for Mac – Error Checking Numbers as Text

by Greg Johnson, posted in Software, Tech Tips

By default, Microsoft Excel has error checking that will place a warning indicator next to all cells that have numbers in a cell formatted to be text. It’s common to have serial numbers and other entries that will contain text and numbers in the same field, so having a warning about it is unnecessary.

To disable this and turn it off, in the Mac version, go to the Excel Menu and choose Preferences > Error Checking > and remove the check next to “Numbers formatted as text” as shown below.

20150801sa-microsoft-excel-error-checking-numbers-as-text