Excel Tricks

Excel Error – Unable to read file followed by file format or extension is not valid

by
Sometime, the users suddenly start getting the error- “Unable to read file.”, every time a new excel sheet is opened.
In such situation, generally, the error appears every time the user opens an existing or even new excel file.

On clicking OK, another error is displayed as below.

When clicked Ok, the file opens fine.
Following is the solution which worked for me:
  1. Search for the “XLStart” folder in machine. It can also be at multiple locations.

    2. C:UsersAppDataRoamingMicrosoftExcelXLStart

  2. Delete the file – “~$Book.xltx”. or other corrupted file as displayed in the message.
  3. Open excel again to verify that the error message is not displayed now.

How to recover an un-saved Microsoft Office Excel, Word, or PowerPoint file

by
How often have you faced a situation when you worked upon a new MS office excel/word/PowerPoint file and accidentally close the document before saving or worse lose power and the system shuts down – not your fault? I am sure there would not be a single person on this planet that never had this crunch.
Here is the good news…. 🙂
Microsoft Office has a fantastic feature built in where it can recover unsaved new documents!
To use this feature, ‘Auto Recover ‘ and “Auto Save” features should be enabled. Below are the steps to do so:-
1.     Click the File tab.
2.     Under Help, click Options.
3.     Click Save.
4.     Make sure the Save AutoRecover information every x minutes check box is selected.
5.     In Word 2010, Excel 2010 and PowerPoint 2010, make sure the Keep the last autosaved version if I close without saving check box is selected.

Below are the steps to take to recover your unsaved new document in Microsoft Office Excel, Work, or PowerPoint 2010 if you inadvertently close it before saving it:

1.     Open the Office 2010 applications that you were using.
2.     Click the File tab.
3.     Click Recent.
4.     Click Recover Unsaved Documentsif you are in Word 2010, Recover Unsaved Workbooks if you are in Excel 2010 or Recover Unsaved Presentations if you are in PowerPoint 2010. 

5.     Your saved drafts folder will open in a new window. Select the file and then click Open.
6.     In the business bar at the top of the file, click Save As to save your file to your computer. 
Below are alternative steps to access these files:
1.     Open the Office 2010 application you were using.
2.     Open a new file or any existing file.
3.     Click the File tab.
4.     Click Info.
5.     Click Manage Versions.
6.     Click Recover Unsaved Documentsif you are in Word 2010, Recover Unsaved Workbooks if you are in Excel 2010 or Recover Unsaved Presentations if you are in PowerPoint 2010. 

7.     You will see the list of your unsaved files. Select the file and click Open.
8.     In the business bar at the top of the file, click Save As to save your file to your computer.

Another alternative!!!  Depending on the operating system, files may also be browsed at following locations:

  • Windows 7/Windows Vista
    C:UsersUser_NameAppDataLocalMicrosoftOfficeUnsavedFiles
  • Windows XP
    C:Documents and SettingsUser_NameLocal SettingsApplication DataMicrosoftOfficeUnsavedFiles 

I read somewhere, Ctr+S is still your best friend…:D

Move Data Between Excel Rows and Columns – Excel Tricks

by
Let’s suppose we have some data in rows like:-

Now, we want to change it into columns, like:-

MS-Excel
Excel – Transpose Data

Moving the data manually, one item at a time, would be a tedious process when we have large volume of data. To save time, we can use the Paste Special command to quickly transpose a column of data to a row of data, or vice versa.

We need to follow the following steps:-

  1. Copy the data in one or more columns or rows.

 
2.     Right-click your first destination cell (the first cell of the row or column into which we want to paste our data), and then click Paste Special.

3.    In the Paste Special dialog box, select Transpose, and then click OK.
We’ll find the Transpose check box in the lower-right corner of the dialog box:

4.    Starting with our first cell, Excel pastes the data into a row or column.

There are few points to be noted here:-
  • Columns and rows can not overlap. For example, if we select values in Column C, and try to paste them into a row that overlaps Column C, Excel displays an error message. When we go to paste a column or row, the destination area must be outside of the original values.
  • If we copy and paste data from more than one column, Excel places the data from the leftmost column on top. For example, say we copy data from columns A and B of a worksheet, and we  paste the data into another worksheet starting at row 1. Excel places the data from column A in row 1, column B in row 2, and so on.
  • If we don’t see the Paste Special command, make sure you right-click the first destination cell. We’ll also find the command on the Edit menu. If westill don’t see the command, make sure we’re using Excel 2000 or later.