Someone sent me a Microsoft PowerPoint presentation to review. The file
I received was a .pptx file which I opened using the PowerPoint application
in Microsoft Office 2016 for Mac on my
OS X El Capitan (10.11.6). When I viewed the presentation, I noticed one
of the slides was blank. I sometimes receive Microsoft Excel workbooks which
contain a worksheet that should normally contain network diagrams where the
diagrams don't appear when viewed on my Mac laptop, but do appear when I open
the file in Microsoft Excel on a system running the Microsoft Windows operating
system. In such cases, I've found that since the .xlsx or .xlsm
format is just an XML-based container format akin to a
zip file, I
rename the files where the problem occurs to have a .zip rather
than .xlsx or .xlsm extension, which then allows me to extract the
files contained within the file - see
Zipping and unzipping Excel
xlsx files and Extracting
embedded documents from an Excel .xlsm file. Then I use the OS X
Terminal window to examine the .bin files in the xl/embeddings
subdirectory that is produced when I extract the files and folders
from the zip file. That utility tells me which of the .bin files
represent embedded Microsoft Visio or PowerPoint files, so I can then give
the Visio ones a .vsd extension rather than a .bin extension. I can
then view the diagrams with the free
VSD Viewer Pro application
I have on the Mac. Since there are usually several .bin files in the directory,
I created a Python script
to determine the file type for all of the files in a directory at once.
If you see an error message like the one below, which was produced by Microsoft
Excel for Mac 2016 on a Mac OS X system, even though you don't have the file
open currently, then you will need to delete the lock file, which
should be in the same directory as the spreadsheet.
This file is locked for editing.
Locked by: John Doe
You can open the file as read-only.
The lock file will have the same name as the workbook you were trying
to open, but the file name will have ~$ prepended to it. To
delete the file you will need to "escape" the meaning of the dollar sign
by putting an escape character, i.e., a backslash character, immediately
before the dollar sign. I.e., use ~\$ as shown below:
$ ls -alg **SGRS_2017.xlsm
-rw-rw-r--@ 1 ABC\Domain Users 761327 Sep 13 15:57 SGRS_2017.xlsm
-rw-rw-r--@ 1 ABC\Domain Users 171 Sep 18 22:46 ~$SGRS_2017.xlsm
$ rm ~$SGRS_2017.xlsm
rm: ~.xlsm: No such file or directory
$ rm ~\$SGRS_2017.xlsm
Once the lock file has been deleted, you should be able to open the
file without the warning message that it is locked for editing.
You can use the LEFT and RIGHT functions in
the Microsoft Excel
spreadsheet program along with the LEN (length) function to remove the leftmost
or rightmost character from a text
string. These functions also work in
which is the spreadsheet component of the
Apache OpenOffice Calc, which is the spreadsheet program included in
though in the Apache OpenOffice Calc program you need to substitute semicolons
(;) for commas (,) in the formulas. E.g.,
in Apache OpenOffice Calc you would need to use =RIGHT(A5;LEN(A5) -1)
, instead of =RIGHT(A5,LEN(A5) -1) as you would in the other
Removing the leftmost character
The syntax for the RIGHT function is RIGHT(text,[numchars]).
If you don't include numchars, i.e., you use RIGHT(text)
then the value returned is the rightmost character in the string. E.g.,
if cell A1 has 1ABC in it, then =RIGHT(A1) returns
C. But suppose, instead, you want to remove the leftmost
charaacter from a string. You can use the RIGHT function to do
so. E.g., suppose I have a column of values, e.g.:
If I want to remove the number at the beginning of each text
string and put the shortened strings in column B, I could, since the
strings are all 4 characters long, use =RIGHT(A1,3) in column B1
and then copy the formula down through the other cells in column B by clicking
in cell B1 and holding down the leftmost mouse button and dragging downwards
through the other cells in column B and then hitting Ctrl-D.
But suppose the strings vary in length. I.e., suppose I have a worksheet
containing the following strings in column A:
Microsoft Excel supports formatting
in a workbook as a date followed by a time with an AM or PM value, i.e., the
times are in 12-hour clock format where noon is 12:00 PM and
midnight is 12:00 AM. You can select that format for a cell or
column in a worksheet by highlighting the cell(s) or column (a
column can be selected by clicking on the letter at the top of the
column) and then clicking on Format then Cells and
then clicking on Date and selecting a type of "3/14/15 1:30
If you wanted to extract just the hour from the timestamp, you can use
the HOUR function, e.g. =HOUR(A1) to extract the hour
value from cell A1. The hour will be displayed in 24-hour clock format, aka
military time, i.e., 7:00 AM is 7 while 7:00 PM
is 12 plus 7, i.e., 19; 12:00 AM (midnight) is 0 and 12:00 PM (noon) is 12.
If you wish to know whether a cell holds a
Uniform Resource Locator (URL), such as http://example.com, in
Google Sheets you can use the ISURL function.
E.g., if I wanted to know whether cell A5 contains a URL, elsewhere in the
spreadsheet I could use the formula =ISURL(A5). The value returned
will be either TRUE or FALSE. Note: this will return
value TRUE or FALSE only if the text in the cell is a URL. If, instead, I
have =hyperlink("http://superuser.com","Super User"), i.e., I
have text in the cell that is hyperlinked, the value will be FALSE, because, in the
example, the text that appears in the cell will be Super User.
This function is not available in
Microsoft Excel, at least as of Excel 2013 for Windows and Microsoft
Excel for Mac 2016 (version 15.29), which is part of
Microsoft Office 2016 for OS X and macOS systems. Nor is it available for
Calc, at least as of version 4.1.1. If you try using ISURL as a formula
in those applications, you will see #NAME? appear in the cell
where you place the formula, since its usage is an incompatibility between
those versions and Google Sheets.
The ISURL formula will return TRUE for other URLs besides HTTP or HTTPS
ones. E.g., FTP and mailto URLs will also result in a value of TRUE.
E.g., if a cell contains any of the following URLs, an ISURL formula that
checks the content of the cell will return TRUE.
I wanted to add another sheet to an
Excel workbook, but when I clicked on the "+" to add the additional
worksheet, I saw the message "Workbook is protected and cannot be changed."
I didn't know what the password might be because I had never set a
password for the workbook, which I had created in a prior version of Excel;
I'm using Microsoft Office 2016 on a
Pro running OS X El Capitan (10.11.6) at the moment.
To add a
checkbox (check box, tickbox, tick box) column in
Microsoft Excel, you will need to first enable the developer ribbon
in Excel, if it is not already enabled. To do so, take the following steps in
Excel (the steps were written for Excel 2010 and 2013, but may be similar for
I often receive Microsoft Excel files that have documents created by
other Microsoft applications embedded within them. E.g., at the top of
a worksheet I may see something like =EMBED("Visio.Drawing.11","").
Sometimes I want to extract the embedded file. With a Microsoft Excel .xlsm
file that is easy to do, because XLSM is a
XML-based file format. To extract embedded documents, such as
PowerPoint presentations, I make a copy of the .xlsm file then rename the
copy's extension from .xlsm to .zip. I can then extract the contents of the zip
file. Within the directory that holds the extracted files, there will be a
xl directory. Within that directory there is a media
directory and within the media directory there is an
embeddings directory that holds the embedded files, such as the
Visio drawings in the example below.
After Microsoft Excel for Mac 2011, which is part of the
Microsoft Office for Mac 2011 office suite, crashed on my
MacBook Pro laptop running OS X Yosemite (10.10.5), whenever I tried to reopen one of
the workbooks I had open at the time of the crash, I would see the message
"Microsoft Excel quit unexpectedly."
The crash report showed an exception type
of "EXC_CRASH (SIGABRT)" and "Application Specific Information" was "
If I tried opening the workbook with the
Apache OpenOffice 4.1.2 on the same system, it displayed "Loading..."
and then seemed to get stuck there. However, when I returned to the system
about 15 minutes later, the spreadsheet I needed to access was open within
Calc application. All of the sheets within the workbook were
accessible and I clicked on File, then chose Save
and then saved the file with the default file type of "ODF Spreadsheet (.ods)",
so that I would have a readable copy of my data that I could at least open
with OpenOffice. I then clicked on File again and this time chose
Save As and used a file type of "Microsoft Excel 2003 XML (.xml)".
I received a warning that "This document may contain formatting or content
that cannot be saved in the Microsoft Excel 2003 XML file format. Do you want
to save the document in this format anyway?" I chose "Keep Current Format" to
save the spreadsheet in the .xml format.
If you are unable to insert new worksheets into an Excel workbook or make
other changes to the workbook because it is protected and you don't know
the password to unprotect the workbook, if the workbook is in a .xlsx file,
you can remove the password by changing the .xlsx
extension to .zip, then extract the contents of the zip file, locate
the workbook.xml file within it and remove the password
between the double quotes following workbookProtection workbookPassword=
using a text editor, then resave the file and recompress the
directory produced when you unzipped the zip file, but, on an OS X system,
you will need to ensure that the directory and its contents are compressed
in the way Excel is expecting, e.g. with the
zipdir Pyhon script.