|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
=POWER(2,3) in
a cell which would yield the value 8. Or you can use the exponent operator, the
caret character, i.e., ^ (shift-6 on a Windows keyboard).
E.g., to calculate 2 raised to the power 3 you could put =2^3
in a cell which produces the value 8.
Then click on the Excel Options button and select the Save option. The checkbox for "Save AutoRecover information every" should be checked. You can then change the frequency from 10 minutes to a more frequent number; I chose to have Excel automatically save a document every 5 minutes.
In cells where you have chosen to present a dropdown list to a user, when the user clicks on the cell or tabs into it, he/she will see a small box with a downward pointing arrowhead appear to the right of the cell. The user can then either type a value in the field or he/she can chose a value from the dropdown list by clicking on the small box with the downard pointing arrowhead. If the user types a value that isn't in the list rather than selecting from the dropdown list, when the user hits enter or moves the cursor out of the cell, he/she will see the message "The value you entered is not valid. A user has restricted values that can be entered in this cell."
[ More Info ]
=IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs
Sheet2:"&Sheet2!A1, ""). I clicked in that cell and then
clicked on Edit and then Copy. Since the columns
in both of the worksheets I wanted to compare extended to AE with
804 rows, I then selected all of the columns from A to AE and all
rows from 1 to 804 and then clicked on Edit and then
Paste Special with All selected. I then clicked
on OK to copy the formula throughout the new worksheet.
Excel automatically updates the references so that B2, for instance,
gets the formula =IF(Sheet1!B2 <> Sheet2!B2,
"Sheet1:"&Sheet1!B2&" vs Sheet2:"&Sheet2!B2, "").
Excel then showed the differences between Sheet1 and Sheet2 in Sheet3
where I had used a formula to compare cells in the two other sheets.
If the contents of a cell differed, Excel showed the differences.
E.g. for cell A71, I saw Sheet1:I13-0003 vs Sheet2:I97-0033, since
Sheet1 had I13-0003 in that cell whereas Sheet2 had I97-0033
. If the cells matched, the corresponding cell in Sheet3 was empty.
So, even though the Mac version of Excel doesn't include the workbook comparison feature found in Windows versions of the program described at How to compare two Excel files for differences, you still may be able to compare sheets in two Excel files by copying relevant sheets into a new sheet where you can see the differences displayed. In the exmple above, the contents of E71 in Sheet3 showed the values for the other sheets as numeric values, though there were dates in the corresponding cells in Sheet1 and Sheet2.
[ More Info ]
If you have an Excel workbook containing two cells that contain a date and time and you want to know the time difference between them in days and hours, you can subtract one from the other and get the elapsed time between the two timestamps in days and hours by using a custom date and time format for the cell that will hold the results. E.g., suppose I have a spreadsheet with the following timestamps in columns A and B:
| A | B | C | |
|---|---|---|---|
| 1 | Start Time | End Time | Elapsed Time |
| 2 | 1/1/18 0:01 | 3/1/18 15:03 | |
| 3 | 2/6/18 15:18 | 2/7/18 18:07 | |
| 4 | 3/1/18 7:55 | 3/1/18 13:01 |
The cells containing the date and time have the custom format
m/d/yy h:mm.
[ More Info ]
[ More Info ]
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
Filename: SGRS_2017.xlsm
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
Google Sheets,
LibreOffice Calc,
which is the spreadsheet component of the
LibreOffice software
package, and
Apache OpenOffice Calc, which is the spreadsheet program included in
Apache OpenOffice,
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
programs.
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.:
| A | B | |
| 1 | 1ABC | |
| 2 | 2DEF | |
| 3 | 3GHI | |
| 4 | 4JKL | |
| 5 | 5MNO |
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:
| A | B | |
| 1 | 1ABC | |
| 2 | 2DEFG | |
| 3 | 3HI | |
| 4 | 4JKL | |
| 5 | 5MNOP |
[ More Info ]
Microsoft Excel supports formatting timestamps 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 PM."
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.
[ More Info ]
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
the Boolean
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
Apache OpenOffice
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.
http://example.com
https://www.example.com
ftp://ftp.microsoft.com
mailto:someone@example.com
Shop Amazon Local - Subscribe to Deals in Your Neighborhood
