MoonPoint Support Logo

 

Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
June
Sun Mon Tue Wed Thu Fri Sat
           
18 19 20 21 22
23 24 25 26 27 28 29
30            
2024
Months
Jun
Jul Aug Sep
Oct Nov Dec


Wed, Mar 20, 2024 9:39 pm

Location of Microsoft Excel recovery files

Microsft Excel can automatically save updates to spreadsheets. If Excel, or the system, crashes, when you reopen Excel you will see a Document Recovery pane on the left side of the Excel window with the message "Excel has recovered the following files. Save the ones you wish to keep."

Excel Document Recovery - Collectorz

Some of the documents listed may have been saved by the user without any subsequent changes being made and will have "Version created last time the user saved the file" beneath the file name as shown above.

[ More Info ]

[/software/office/excel] permanent link

Sun, Aug 14, 2022 10:01 am

Excel Proper Case

I inherited an Excel spreadsheet containing names and addresses for all the members of an organization where all the letters for the names and addresses were capitalized. I wanted to convert the names and addresses to "proper case" where only the first letters of names, streets, and cities are capitalized (in the Python programming language proper case is known as "title case"). Fortunately, Excel provides a function, propercase, to perform that function. To perform the conversion, I inserted a new column to the right of each of the columns where all uppercase letters were used. The first column in the Excel workbook contained the last names for the members. There was a header titled "Last Name" in cell A1 with the first person's last name appearing in cell A2, so in the newly inserted column, B, I put =proper(A2) in cell B2. When I hit Enter, instead of AIKIN, JR in A2, I then had Aikin, Jr in B2. I then clicked in cell B2 and dragged the mouse pointer downwards to the last row containing a member's contact information, which resulted in all the members last names being placed in column B with the proper case, i.e., uppercase letters for the first letter of the name and lowercase letters for the rest of the name. I couldn't just delete column A then, which would cause a #REF! message to appear in column B for every name, since I was using a formula that depended on a value being in column A. So, instead, I copied all of column B by clicking on the B at the top of the column to highlight all entries in the column. I then used the Ctrl-C keys to copy all of the entries. I then needed to paste the values in column B into column A, rather than pasting in the formulas. You can do so, by right-clicking on the column designator letter, i.e.the A for column A in this case, and then choosing the appropriate paste option, To paste just the values and not the formulas and formatting, you can click on the icon of a clipboard with "123" on it.

Excel Paste Options

Or you can select Paste Special and then click on the same icon that appears beneath Paste Values.

Excel Paste Special

Once you've pasted the values that are in proper case, you can delete the column, e.g., B in this case, where the formulas are used.

Note: for addresses with Post Office boxes, any instance of "PO BOX" would be replaced with "Po Box," but you can use Excel's find and replace feature to search for all instances of "Po Box" and have it replace all those occurences with "PO Box".

[/software/office/excel] permanent link

Fri, Jun 24, 2022 10:22 pm

Raising a number to a power in Microsoft Excel

There are two ways to use exponentiation, i.e., to raise a number to a power in the Microsoft Excel spreadsheet program. You can use the POWER(number, exponent) function, e.g., to raise 2 to the power 3, you could put =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.

[/software/office/excel] permanent link

Mon, Mar 01, 2021 9:44 pm

Changing the AutoRecover frequency for Microsoft Excel 2007

Microsoft Excel 2007 crashed on a laptop running Windows 10 that I was using. When I restarted Excel, I found that, unfortunately, I had lost all of the recent changes I had made to a spreadsheet, even though Excel put "(version 1).xlsb [Autosaved]" in the title of the spreadsheet I had been working on when I restarted Excel—-it crashed when I attempted to paste a webpage URL into a Hyperlink field. The crash and loss of my recent work was aggravating, so I decided to change the frequency with which Excel auomatically saves a file in an AutoRecover version that will allow you to automatically recover a document if if the program hangs or crashes. To change that setting for Excel 2007 on a Windows system, you can click on the Office Button at the top, left-hand corner of the Excel window (it is to the left of the "Home" tab as shown below).

Excel - Office Button

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.

Excel 2007 AutoRecover Frequency

[/software/office/excel] permanent link

Wed, Dec 16, 2020 9:15 pm

Creating a dropdown list in Microsoft Excel

In the Microsoft Excel spreadsheet program, if you wish to create a dropdown list where a user can select options from the list for a cell's value, you can take the steps below:
  1. Highlight the cells where you wish to have the the dropdown list appear, e.g., by clicking in a cell and dragging downwards through a column where a user should select from the dropdown list.
  2. Click on Data on the menu bar at the top of the Excel window
  3. Click on Data Validation.
  4. If you see 3 options under Data Validation, i.e., Data Validation, Circle Invalid Data, and Clear Validation Circles, select Data Validation.
  5. You will then see a window where you can change settings. In the "Allow" field for validation criteria, select List.
  6. You will then be given an option to provide the items for the list in the source field. If you have just a couple of options for the list that won't change, you can type them separated by a comma.
  7. Click on OK.

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 ]

[/software/office/excel] permanent link

Fri, Nov 15, 2019 7:47 pm

Comparing Excel workbooks in Microsoft Excel for Mac

I needed to compare two Excel workbooks produced with Microsoft Excel for Mac (version 16.29) on my MacBook Pro laptop. Unfortunately, the MAC version of Excel doesn't include a capability to directly compare two workbooks. Since both workbooks only had one worksheet in them, I created a new workbook and then copied the contents of the worksheet in the first workbook to Sheet1 in the new workbook and the contents of the worksheet in the second workbook to Sheet2 in the new workbook. I copied the contents of the worksheets by selecting Edit and then Select All in a worksheet and then pasting the contents into a sheet in the new workbook. I then created a third worksheet, Sheet3 in the new workbook. In cell A1 in that workbook, I put the formula =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 ]

[/software/office/excel] permanent link

Thu, Mar 01, 2018 10:44 pm

Determining the the elapsed time between two timestamps in Excel

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 ]

[/software/office/excel] permanent link

Thu, Feb 15, 2018 10:22 pm

Enabling the Developer Tab in Excel for Mac 2016

To be able to view the Developer Tab in Microsoft Excel for Mac 2016, take the following steps:
  1. Click on Excel at the top, left-hand corner of the Excel window and the select Preferences.
  2. On the Excel Preferences window, click on View in the Authoring section.
  3. In the View window, click on the check box next to Developer tab, which you will see in the In Ribbon, Show section.
  4. You can close that window by clicking on the "x" in the red circle at the top, left-hand corner of the window. You should then see Developer as a selectable option to the right of Data, Review, and View on the menu bar at the top of the Excel window.
  5. If you click on the Developer tab, you should see options that include Visual Basic, Macros, Record Macro, Add-ins, Excel Add-ins, Button, Group Box, Combo Box, Label, Check Box, Scroll Bar, List Box, Option Button, and Spinner.

[ More Info ]

[/software/office/excel] permanent link

Tue, Jan 16, 2018 10:30 pm

Can't insert worksheet in Microsoft Excel for Mac 2016

When I tried to insert another worksheet in an Excel workbook today, I found that the option to insert a new blank worksheet was grayed out when I clicked on Insert and selected Sheet.

Excel 2016 - insert sheet grayed out

When I tried another means of adding a new sheet by clicking on the plus sign at the right side of the list of sheets in the worksheet to add the sheet, I saw an alert message stating "Workbook is protected and cannot be changed."

[ More Info ]

[/software/office] permanent link

Mon, Nov 20, 2017 10:22 pm

EMF image embedded in a PowerPoint file on OS X

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 MacBook Pro laptop running 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 file 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 file command in a 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.

[ More Info ]

[/software/office/powerpoint] permanent link

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo