After installing MS12-030 (http://support.microsoft.com/KB/2597166 for Excel 2010 or http://support.microsoft.com/kb/2597162 for Excel 2007) you may notice a problem with files that contain links to other spreadsheets.

This problem won't happen to all users and may not affect all of the links in the spreadsheet, but some of the links may be affected the following way:

Spreadsheet 1 is located in Folder 1 and contains a UNC link to Spreadsheet 2 which is located in folder 2. After applying this security update you may find that Spreadsheet 1 now links to the same file, but in Folder 1 where it doesn't exist.

More Information

This problem only affects UNC links and only affects the new Office file format - in other words XLSX, XLSM, etc. The legacy XLS file format is not affected.

Fix

The following fix will resolve this problem. You must apply the August 2012 cumulative update and recreate the links.

http://support.microsoft.com/kb/2730388

After you install MS12-030 (Vulnerabilities in Microsoft Excel could allow remote code execution - http://support.microsoft.com/kb/2663830) you may see the following error message appear when trying to do sorts in your spreadsheet: “Excel cannot complete this task with available resources. Choose less data or close other applications.”

Updated - 6/28/2012 - a fix for this problem is now in the June Cumulative update located here - http://support.microsoft.com/kb/2712235 and fixes the problem described here - http://support.microsoft.com/kb/2687317.

This problem happens when you highlight the entire spreadsheet by clicking the upper left button to select all cells (circled in red below).

When you select all of the cells using this button and then perform a custom sort, the error message appears on 32-bit versions of Excel. On 64-bit versions of Excel, the sort won't produce an error, but it will take a longer time than expected to perform the sort operation.

Note: If you have installed the update you will want to remove the registry key.  Having the update and the regkey will still exhibit the behavior. 

UPDATE 4/19/2013:  This is just one of the reasons that you may get this error message.  There has been a new KB article that addresses many other cases for the error see the following KB for more information.  

2779852 How to Troubleshooting Available Resources errors in Excel
http://support.microsoft.com/kb/2779852

Any veteran of Excel has probably seen this error dialog:

And dealing with this can be a major pain. What makes resolving this particular problem difficult is that there are multiple causes for the problem, and thus multiple solutions. This article attempts to break the file locking problem down categorically so that it's easy to diagnose and fix. Please feel free to leave any feedback!

Things to do to resolve the problem

I. Install the latest rollup and updates

Install the update in the following rollup and then install the three updates referenced in the rollup article (total of four updates).

Note: Before installing these updates you must first install SP1 for Windows 7 or Windows 2008 R2 (http://support.microsoft.com/kb/976932).

Rollup article:

"An enterprise hotfix rollup is available for Windows 7 SP1 and Windows Server 2008 R2 SP1"
http://support.microsoft.com/kb/2775511/EN-US

These are the individual links to the 4 updates to install. You do not need the reg keys mentioned in the article as they are for the specific issues listed with the reg keys which is unrelated.

Update for Windows Server 2008 R2 for Itanium-based Systems (KB2775511)
http://catalog.update.microsoft.com/v7/site/Search.aspx?q=2775511

Update for Windows 7 (KB2775511)

http://catalog.update.microsoft.com/v7/site/Search.aspx?q=2732673

Update for Windows 7 for x64-based Systems (KB2775511)

http://catalog.update.microsoft.com/v7/site/Search.aspx?q=2728738

Update for Windows Server 2008 R2 x64 Edition (KB2775511)

http://catalog.update.microsoft.com/v7/site/Search.aspx?q=2878378

II. Install the latest Cumulative Update for Excel

This is where you go to find cumulative updates that are available from the Microsoft Office team to fix reported problems:

In Excel:

1. Click File > Open
2. Click Organize > Layout
3. Uncheck 'Details Pane' and 'Preview Pane'

If Outlook is involved:

1. Open a new e-mail.
2. In the "Include" group, click Attach File.
3. Click Organize > Layout 
4. Uncheck 'Details Pane' and 'Preview Pane'.

IV. Add the OpLocks registry keys

Add the following two registry keys to the client machines and then reboot the system:

Key: HKEY_CLASSES_ROOT\CLSID\{993BE281-6695-4BA5-8A2A-7AACBFAAB69E}
Name: EnableShareDenyNone 
Type: REG_DWORD 
Value: 1

Key: HKEY_CLASSES_ROOT\CLSID\{993BE281-6695-4BA5-8A2A-7AACBFAAB69E}
Name: NoOpLock
Type: REG_DWORD 
Value: 1

Note: Both of the above keys must be added for this solution to work.

V. Install operating system updates

1. For Windows 7, Windows 7 Sp1, Windows Server 2008 R2 or Windows Server 2008 R2 SP1 -

"ERROR_SHARING_VIOLATION" error message in Windows XP or in Windows Server 2003 when you try to open a file on an SMB share on a server that is running Windows 7 or Windows Server 2008 R2"

http://support.microsoft.com/kb/2625434

2. For Windows 7, Windows Server 2008 R2

"Temporary files do not synchronize correctly to a non-DFS share on a server from a client computer that is running Windows 7 or Windows Server 2008 R2"

http://support.microsoft.com/kb/2434932

3. For Windows 7, Windows 7 Sp1, Windows Server 2008 R2 or Windows Server 2008 R2 SP1 -

"You cannot access a shared file by using the SMB Version 2 protocol because of a race condition in Windows Server 2008 R2 or in Windows 7"

http://support.microsoft.com/kb/2618190

4. Windows Vista, Windows Server 2008 -

"You share some files on a computer that is running Windows Server 2008 or Windows Vista. Some users try to access these shared files. In this scenario, the computer may restart unexpectedly. Additionally, you receive a Stop error that resembles the following: STOP 0x0000004E (0x0000009A,parameter2, parameter3, parameter4 )PFN_LIST_CORRUPT"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;2447725

5. Windows Server 2003 -

"You have a shared file on a computer that is running Windows Server 2003, Two applications access this shared file concurrently. One application has write access to the file, and the other application has read-only access to the file.After both applications run for some time, such as several hours, the application that has write access cannot write data to the shared file, and this application receives the "Delayed Write Failed" error message. This problem causes data loss or data corruption in the shared file."

http://support.microsoft.com/kb/971295

VI. Use MSConfig to stop third party programs and services, including antivirus software

1. Go to START, and from the RUN line, type MSCONFIG and hit ENTER.
2. The System Configuration Utility will launch. On the General tab, click the Selective Startup option. Then uncheck the boxes labeled "Load Startup Items" and "Load System Services".
3. Click on the Services tab. Find the column header at the top labeled "Service" and left click that column header to sort these services in alphabetical order. Scroll down in the list and find a service called "Windows Installer" and click it so that it has a check in the square box.
4. Click Apply and when the changes are made, the OK button will read "Close". Click this button and you will asked to reboot the machine.
5. After the machine has restarted, click OK when the System Configuration Utility information dialog appears. If the System Configuration Utility itself appears, click the red X at the top right corner of the dialog box. If you are prompted to restart the machine, click the button labeled "Exit Without Restart".

This should get the machine into a state where third party and non essential programs are not started when the computer boots. This includes anti-virus software. Try accessing shared files with these programs shut off.

To get the machine back to Normal mode, open MSConfig again as in Step 1 and on the General tab click the

“Normal” startup option. Click Apply, the Close and reboot the machine.

In cases where the wrong user name is displayed in the file lock dialog

Sometimes where it says 'another user' it has the name of someone that didn't open the file or it has no name at all. Just a big ' ' where a name ought to be. Fear not, there is a fix for this problem. The flavor depends on whether you have Excel 2010 or Excel 2007. The below KB Articles contain hotfixes that address several issues, not just the file locked issue. But in the dialog of the article you will see this as one of the problem descriptions:
 
Assume that you have an .xls file that is protected by Information Rights Management (IRM) on a network share. When you open the file in Excel 2007, a File In Use dialog box appears. However, the dialog box does not display the correct name of the user who locked the file. Therefore, you cannot edit the file.
 
Understand that these fixes don't make the File in Use dialog go away, they just correct the problem of showing bogus user name information on the dialog itself.
 
Excel 2007 - http://support.microsoft.com/kb/2598133
 
Excel 2010 - http://support.microsoft.com/kb/2598143

Additionally, we have seen this issue when the owner file is not deleted from the directory upon closing the file. The file looks like ~file name.xlsx and contains the user information for who had the file open. . This can happen if something interrupts the process and breaks Excels connection with the file.

An example of this would be if a user had the file open from a server and the server was then rebooted. This causes the connection between the Excel application and the owner file to be broken. When the user then closes the file, Excel does not see the need to remove the owner file from the directory, and this results in the orphaned file. When another user later tries to open the same file from that same directory with the orphaned (~filename.xlsx) file, Excel refers to the existing owner file for the information to present to the user in the file locked dialog, resulting in the incorrect user information being presented. To resolve this issue, you need only to delete the orphaned ~filename.xlsx file from the directory.

Considerations for NetApps storage devices

Note: If the issue is only occurring when using a NetApp or other third party storage, we would recommend engaging the Support Team for the third party technology for additional troubleshooting and assistance.

We have seen cases involving the file locking issue when moving files to a NetApps storage device. First and foremost - update your NetApps device to the latest version. The file locking problems can be fixed just by updating the NetApps device to the latest version. If that fails or if you have the latest version, there are a couple of other things to try. This information was documented at http://netappsky.com/netapp-storage-management/cifs-performance-is-slow-after-investigating-performance-issues-cifs-considerations/ but has since been taken down.

I. Enable oplocks

Make sure that cifs.oplocks.enable is on. Oplocks (opportunistic locks) enable a CIFS client in certain file-sharing scenarios to perform client-side caching of read-ahead, write-behind, and lock information. A client can then read from or write to a file without regularly reminding the server that it needs access to the file in question. This improves performance by reducing network traffic. CIFS oplocks on the filer are on.  By default: You might turn CIFS oplocks off under the following circumstances otherwise, you can leave CIFS oplocks on. 
 
1. You are using a database application whose documentation recommends that oplocks be turned off.
2. The CIFS clients are on an unreliable network.
3. You are handling critical data and you cannot afford even the slightest data loss.
 
sasfiler*> options cifs.oplocks.enable on
 
Make sure that this option is set before the clients connect to the filer.   If they are already connected, do a cifs terminate and cifs restart on the filer.
 
sasfiler*> options cifs.neg_buf_size 33028

II. Set cifs.oplocks_opendelta

Change the cifs.oplocks_opendelta setting. Under certain workloads setting cifs.oplocks_opendelta to 0 can improve CIFS throughput performance by 3% to 5%. Again, if setting this value to 0 results in client disconnects, reset it to 8 (the default value).  Changing the delay time for sending oplock breaks.  If a client that owns a file oplock sends a file open request, it is temporarily vulnerable to a “race condition” that can occur if the storage system requests an oplock break. To prevent this condition, the storage system delays sending an oplock break according to the delay time value (in milliseconds) specified by the cifs.oplocks.opendelta option.   By default, the default delay time is 0 milliseconds. If your storage system must support some older Microsoft Windows clients, including Microsoft you can set the delay time to another value, such as 8. This means that after the storage system receives or responds to a request to open a file, the storage system will make sure that 8 milliseconds have elapsed before sending an oplock break to that client.   options cifs.oplocks.opendelta time Here, time is the delay in milliseconds.  Setting the cifs.oplocks.opendelta option postpones the sending of oplock break requests to clients that have just opened files. You must consult technical support if you are considering setting this value higher than 35.

sasfiler*> options cifs.oplocks_opendelta 0

sasfiler*> options cifs.oplocks_opendelta 8

For example, try adjusting cifs.oplocks_opendelta to a value of 66 (milliseconds)

You might also want to increase the delay time for sending oplock breaks if you see syslog messages similar to the following:

Mon Jan 21 15:18:38 PST [CIFSAdmin:warning]: oplock break timed out to station JOHN-PC for file \\FILER\share\subdir\file.txt

Considerations for Shared Workbooks 

The primary feature of a shared workbook is to allow multiple users to open and edit the files simultaneously. However, if you have chosen to share your workbooks using the 'Share Workbook' feature in the Review ribbon, you may still experience this type of behavior in particular scenarios due to the way in which Excel opens and saves files.

Examples of scenarios that users may experience a similar message using a [Shared] Excel file simultaneously:

Scenario 1:  User 1 opens the [shared] file. User 2 also opens the [shared] file at the same time as User 1. 
       Result:  A "file is locked" type of message will be provided. 
       Workaround:  An option to "Notify" the user when the file is no longer locked is presented. Choose this option and when the lock is released, the user will be able to then choose to open the file in edit mode. 

Scenario 2:  User 1 saves the [shared] file. User 2 also saves the [shared] file at the same time as User 1. 
       Result:  A "file is locked" or "file is locked, try again later" type of message will be provided. 
       Workaround:  Wait a short period of time to allow the lock to release and try saving the file again.  

Flowchart

The following is a flowchart put together by Kevin Sickler on the Microsoft Excel Support team. It gives direction to this complex issue. Click the picture below to see a larger view:

By Amber Mertes

Recently, we’ve seen an issue frequently occuring where the dialogs such as Save As, Print, File Open, etc. are being hidden behind the Excel 2010 application randomly. Additionally, the issue can occur with other
Office applications such as Word.

This issue occurs when third party add-ins are loaded in the Outlook 2010 application. 

The steps to reproduce the issue consistently involve using the functionality to ‘Save & Send’ a document from the Excel application. Once you do that process, the add-ins in Outlook appear to be keeping focus and
not allowing the Excel dialogs to move to the front.

To work around the issue for now, remove any third party add-ins you may be running in Outlook 2010 and add them back in one at a time to determine which one is the root of the issue.  Once determined, contact the
developer of the add-in for any updates or solutions they may have.

For more information, see the following blog which covers the hidden dialogs with Outlook add-ins and using Email functionality within the Office apps:

http://blogs.msdn.com/b/dhruvkh/archive/2012/05/14/outlook-ribbon-woes-on-performing-send-as-e-mail-via-other-office-2010-apps.aspx

Note: This issue does also occur with Office 2013.

This problem isn't a common one, but it is still worth mentioning as there could be other symptoms manifested for this problem. The issue occurs when you try to save or close an Excel file. Excel may become unresponsive or simply hang, requiring you to enter the Task Manager and kill the process.

This problem can occur if you have a bad printer installation. Excel apparently checks the default printer driver when doing a save or close of a workbook. When you try to change the default printer, remove the printer or reinstall it you may encounter difficulties such as an Access Denied error. To resolve the problem, do the following:

1. From the Control Panel go to Programs and Features. Some printers will be listed in here. If yours is listed, right click it and left click on Uninstall.
If the printer isn’t listed, do the following:

2. Click the Windows START button. Click Devices and Printers.

3. What comes up is a list of printers under the ‘Printers and Faxes’. Find the problem printer in the list.

4. Right click on the printer and left click 'Remove Device'. This should uninstall the printer for you. If the printer is still listed, you may have to do it manually:

5. Right click on the printer and left click  Printer Properties. Find the ‘Drivers’ tab.

6. Shown is a list of drivers. These driver DLLs would need to be deleted from the machine. Some of them may exist under System32. I would recommend renaming these to the .OLD file extension instead of deleting them.

7. If removing anything from under System32, a reboot of the machine may be necessary.

By Laura Holzwarth

A new security update (MS12-027)  released, April 10 has been released that affects MSCOMCTL.OCX, which means this not only affects Excel, but can affect all of the Office applications including Word and Power Point.

When you enable macros in an Excel 2003, 2007, 2010 you will receive the following error messages;  

followed by

or

Fixit is now live

The FixIt for this issue is now live.

http://support.microsoft.com/kb/2703186

However, you may also follow the steps to manually fix this problem after the update by following the directions below.

Steps to follow after update

After installing the update you may need to follow the following steps to get rid of the error message listed above.

You install this security update on a computer that has a third-party software solution installed. The software solution is based on Microsoft Visual Basic for Applications (VBA). The software solution creates an instance of the control directly through Microsoft Office. In this scenario, the control may not load in your solution.

To resolve this issue, you must delete the cached versions of the control type libraries (extender files) on the client computer. To do this, you must search your hard disk for files that have the ".exd" file name extension and delete all the .exd files that you find. These .exd files will be re-created automatically when you use the new controls the next time that you use VBA. These extender files will be under the user's profile and may also be in other locations, such as the following:

%appdata%\Microsoft\forms

%temp%\Excel8.0

%temp%\VBE

Scripting solution

Because this problem may affect more than one machine, it is also possible to create a scripting solution to delete the EXD files and run the script as part of the logon process using a policy. The script you would need should contain the following lines:

del %temp%\vbe\*.exd

del %temp%\excel8.0\*.exd

del %appdata%\microsoft\forms\*.exd

del %appdata%\microsoft\local\*.exd

del %appdata%\Roaming\microsoft\forms\*.exd

del %temp%\word8.0\*.exd

del %temp%\ PPT11.0\*.exd

For more information

Please see the article that applies to your version of Office.

2597112 MS12-027: Description of the security update for Microsoft Office 2003 Service Pack 3: April 10, 2012 (http://support.microsoft.com/kb/2597112)

2598041 MS12-027: Description of the security update for 2007 Microsoft Office system: April 10, 2012 (http://support.microsoft.com/kb/2598041)

2598039 MS12-027: Description of the security update for Office 2010: April 10, 2012 (http://support.microsoft.com/kb/2598039)

By Laura Holzwarth

Update: Skype has released a new version of Click to Run (http://www.skype.com/intl/en-us/get-skype/on-your-computer/click-to-call/windows/). Skype will do a silent upgrade to existing customers to fix the problem. The build that will fix this issue is 5.11.0.9874.

Microsoft has also released a KB article that addresses the situation (http://support.microsoft.com/kb/2697462)

The Issue

We are seeing reports that after an update of the Skype ‘Click to Call Add-on’ (Version 5.10.0.9560) clipboard functionality in Office Versions is affected.

Scenario:

When you try to copy and paste in any of the Office applications (Word, Excel, PowerPoint, Outlook) the only option to paste is “Keep Text Only”.  All other formatting options are missing from the toolbar. 

If you attempt use ‘Paste Special’ the following box dialog below displays.

Note: This add-in is not loaded in the Office application, it is loaded in the browser only.  Our testing indicates that if the browser is closed the clipboard formatting features work as expected.

Resolution:

This issue has been reported and they are working on a resolution.  Until then disable or remove the addon.

Disable the Add-on from Internet Explorer or other browser.  In Internet Explorer follow the steps below:

1. Tools – Internet Options
2. Programs tab
3. Manage add-ons
4. Highlight Skype “Click to Call” – disable

To remove the add-on locate the ‘Skype Click to Call’ from the Control Panel and uninstall from the machine.

Consider the following scenario:

When you click a link in Excel to another Office document, you may receive an error saying "Cannot locate the internet server or proxy server." Using Bing, your search brings you to KB Article 218153 (http://support.microsoft.com/kb/218153) which tells you to add a registry key with a value of ForceShellExecute. However after doing this, you may find that when you click the link to another Office document in Excel, you get a spinner that does nothing. It doesn't open the document, it doesn't give an error - nothing happens.

A fix had previously been released for Excel 2007 (http://support.microsoft.com/kb/2596545). But only recently was a fix released for Excel 2010 workbooks "Description
of the Excel 2010 hotfix package (x86 Excel-x-none.msp, x64 Excel-x-none.msp): February 28, 2012" (http://support.microsoft.com/kb/2597142).

The 32-bit version of this patch is: Excel2010-kb2597142-fullfile-x86-glb.exe
The 64-bit version of this patch is: Excel2010-kb2597142-fullfile-x64-glb.exe 

Upon a successful installation, your version of EXCEL.EXE will be updated to build 14.0.6117.5000. 

When trying to paste data into Excel, you may run across this error. The most likely cause is that you have selected a group of cells to copy and then when you want to paste, you have selected another group of cells that are not the same cell size. Usually you can get past this error by selecting only a single cell and pasting to that cell. The cell you selected will act as the top-left most cell in the pasted selection, or the top most cell if it's only a single column of data. However, there is another cause for this error that can be elusive.

Check to make sure that you have not grouped your sheets. There are a couple of ways to tell. First, ungrouped sheets are a different color than the selected, active sheet. In the screenshot below, notice the different color between Sheet1 and the other sheets.

In a grouped set of sheets, notice they are all the same color.

One other way to tell is to look to the top of your workbook. If you see [Group] beside the workbook name, then you know you have grouped sheets. You can't copy a single cell from a group of sheets and paste to a workbook cell with non grouped sheets. What Excel is trying to do is copy the cells you selected from not one workbook, but from all of the grouped workbooks, as if they were stacked. Then when you select a single cell in an ungrouped workbook, Excel determines that you are trying to paste the grouped cells into a single cell on a single worksheet and says "Nope, can't fit all of that there!" Imagine trying to fit a single stack of 100 folded towels into a small suitcase and close the lid. It would be impossible.

To group worksheets, select a worksheet and then hold the SHIFT key down and select another worksheet. You can also hold down the CTRL key to do a relative selection (you can pick and choose). When multiple sheets are selected in this manner, the sheets are automatically grouped and the [Group] warning appears at the top of the workbook. You can ungroup the sheets by selecting only one of the worksheets in a grouping and the rest will turn the darker color. You can also right click a group of selected sheets and left click the "Ungroup Sheets" option.

When you are coding and use a compact method of referring to a pivot table such as:

ActiveSheet.PivotTables(1).

You would expect VBA's intellisense to display the methods and properties of the PivotTables collection when adding the period, however in Excel 2007 and 2010 you get no intellisense whatsoever.

There is a workaround for displaying these methods and properties. Instead of using a compact method in your coding, use the following:

Dim pt as PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.

when you hit the period after pt, you get intellisense and can see what methods and properties are available for this collection. This method also works for other collections that display no intellisense.

After installing the fix from KB article 2596596 (http://support.microsoft.com/kb/2596596) you may find your charts are missing elements when printed. The missing elements can include chart titles, data lables and legends. They are seen on screen and in print preview, but when printed they don't show up.

Right now there is no fix for this problem, but Microsoft is aware and a fix will be addressed in an upcoming update. However, you can use the following workarounds:

1. Select the chart itself and then print only the chart;

2. Save the workbook to PDF format and print from Adobe;

3. Roll back the update you applied.

Thanks to Laura Holzwarth for writing up this content.

Update (2/7/2012) - The fix for this problem will be part of the February Cumulative Update, which will be released the last week of February.

Writing a formula in Excel can be tricky, especially if you plan on writing one that has a bit of length to it and the INDEX and MATCH functions are no exception. There are a couple of things to keep in mind when writing formulas that will make it much simpler.

Consider the following data:

Suppose we wanted to write a formula to look up specific values in this data. We know the lot number that we want to look up (60) and we know we want the data in Column D, the Rate/Day, for that lot number. To begin, work from the inside of the formula, outward. For writing an Index, Match formula you start with the inner-most piece of the formula, the MATCH function.

=MATCH(60, A2:A13,0)

This returns 2. Why? Because we are asking Excel to look in Rows A2 through A13 and return the POSITION NUMBER of lot number 60. In this case we are returning position 2 because lot 60 is in cell A3. Cell A2 is where we told the MATCH function to start looking, so A2 would be position 1 and A3 would be position 2. The zero at the end specifies that we want an exact match of our data.

Okay so now we have the position number of lot 60 in our data. We know we want to return the rate/day for that lot number. That means we want position 4 in our data. Say what??? How did I get 4? Let me explain - when we were looking up the lot number we were looking at rows, or up and down the data. Now we want to look at columns, or left to right in the data. If we start with the lot number being position 1, the location being position 2, etc. we end up with the rate/day being position 4, horizontally. We already have our MATCH formula written so it's time to plug it into our INDEX formula.

=INDEX(A2:E13, [Vertical Position], [Horizontal Position])

We want to include the entire array of data so we use A2:E13. Next, we want to plug in our MATCH formula to the vertical position area of the formula. Why? Because when we did a MATCH formula we were looking at rows, up and down, vertically, for lot 60. Remember now? So our formula now looks like this:

=INDEX(A2:E13, MATCH(60, A2:A13,0), [Horizontal Position])

All that's left is the horizontal position number. We know we want the Rate/Day which is position 4 in our columns of data. So we add it to our overall formula:

=INDEX(A2:E13, MATCH(60, A2:A13,0), 4)

There we go! The formula returns 3.95

Okay let's make this a bit more interesting. What if we didn't know where the Rate/Day was located in our data? In other words, what if we didn't know Rate/Day was in position 4? What if the data was prone to changing and Rate/Day could be in position 2 or position 3? We would need a formula to tell us where Rate/Day is located. How can we do this? You guessed it - with the MATCH function. Instead of looking at lot numbers, however, we would look at the column headers.

=MATCH("Rate/Day", A1:E1,0)

This tells MATCH we want to find Rate/Day horizontally because we are looking from A1 to E1. The zero on the end says we want to match Rate/Day exactly. If we just run this formula we return 4. Now lets put it all together. Again here is our basic formula:

=INDEX(A2:E13, [Vertical Position], [Horizontal Position])

we know we want lot 60:

=INDEX(A2:E13, MATCH(60, A2:A13,0), [Horizontal Position])

we know we want the rate/day for lot 60:

=INDEX(A2:E13, MATCH(60, A2:A13,0), MATCH("Rate/Day", A1:E1,0))

Again, we return 3.95

Congratulations! You've just written your first INDEX, MATCH, MATCH function and understood how it works!

Ever performed a web query in Excel? It's pretty easy. From the Data menu you click 'From Web' in the 'Get External Data' group. Then you enter a hyperlink and choose which parts of the page you want to import. Once you click OK that's it. But what do you do if you should run into an error doing a web query like the following: "Unable to open https://somelocationhere ... Cannot download the information you requested". Fear not. This problem, like many others in life, also has an answer.

In the case of this error Excel is asking URLMON to download a copy of the file given by the web URL (https://www.somelocationhere.com). URLMON has navigated to the URL but the web page contains a header called "pragma: no-cache". This tells WININET, which is used by Internet Explorer, to avoid saving the page to the IE cache. But the file has to be saved to IE for the download to work properly and because it can't, Excel returns the error you see above. And the web query fails. Why would someone set such a thing on a web page? I'm glad you asked. There is an entrie KB Article dedicated to doing this - http://support.microsoft.com/kb/234067.

So what can you do about it? Nothing. You're stuck. Okay, okay that's not true. There are a couple of things you can do.

1. If you have the ability to make changes to the web page, this is by far the easiest method to resolve the problem. Simply remove the "Pragma: No-Cache" header. What does it look like? Look for something like the following:

<%Response.CacheControl = "no-cache" %>

or

<%Response.AddHeader "Pragma", "no-cache" %>

or

<%Response.Expires = -1 %>

2. There is a setting which allows you to bypass the no-cache check for SSL cites on the client. To do this, do the following:

a. Go to START and in the RUN line type REGEDIT.

b. In the registry navigate to

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings

c. Right click Internet Settings and left click New > DWORD Value (32-bit) and name the new value “BypassSSLNoCacheCheck” without the quotes. Double click
this value and give it a value of 1.

That's it. You may need to restart Excel, but you don't need to restart the machine. You should now be able to perform the web query without a problem.

When you create a hyperlink to another Office document in Excel 2007 or 2010 and click to follow that hyperlink, you expect the document to open. In some cases, however, you may receive an error: "Cannot locate the Internet server or proxy server". A quick BING search reveals KB Article 218153 (http://support.microsoft.com/kb/218153). This article directs you to add the "ForceShellExecute" registry value to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\9.0\Common\Internet. And this does take care of that problem. But then another arises.

You may find after applying the ForceShellExecute value that you aren't able to open a hyperlink to an Office document from Excel. Now instead of getting the error, nothing happens. You may get a spinning cursor that does nothing. Fear not! This problem is addressed in two KB Articles. If you're on Excel 2007 then you want to follow KB Article 2596545 (http://support.microsoft.com/kb/2596545) and obtain the hotfix therein which will fix this problem. If you're on Excel 2010, the hotfix is here (http://support.microsoft.com/kb/2597142). There are multiple fixes in this article but most notably is the one where Excel documents contain hyperlinks that do nothing when clicked.  In this article we change the way Excel handles DDE requests to open Office documents. And then after applying either of these fixes, there's one more annoyance that you may or may not be able to live with. You may receive this pop up each time you click the link:

While this dialog isn't a bug, it can certainly be annoying. To get rid of this dialog you need to set the following registry key accordingly:

1. Click START > RUN and type REGEDIT and click OK
2. Navigate to HKEY_CLASSES_ROOT\Excel.Sheet.12

(don’t let the 12 in the above fool you, this applies to Excel, 2010 as well)

On the right look for a value called “EditFlags” – this should be a REG_BINARY key type. Double click it. By default the value is 00 00 00 00 but you want to set it to 00 00 01 00

Once setting this value you will need to close Excel and re-open it. But this will only suppress the dialog for XLSX files. To make the dialog not show up for other files types, you would need to set the EditFlags value above to the following registry keys:

If the EditFlags key doesn’t exist it needs to be created – like under HKEY_CLASSES_ROOT\Excel.CSV - by default there isn’t an EditFlags value so it must be created. The following is a list of the file types and their corresponding registry keys:

XLSB - HKEY_CLASSES_ROOT\Excel.SheetBinaryMacroEnabled.12

XLT - HKEY_CLASSES_ROOT\Excel.Template.8

XLTM - HKEY_CLASSES_ROOT\Excel.TemplateMacroEnabled

XLAM - HKEY_CLASSES_ROOT\Excel.AddInMacroEnabled

XLA - HKEY_CLASSES_ROOT\Excel.Addin

CSV - HKEY_CLASSES_ROOT\Excel.CSV

ODS - HKEY_CLASSES_ROOT\Excel.OpenDocumentSpreadsheet.12

SLK - HKEY_CLASSES_ROOT\Excel.SLK

XLL - HKEY_CLASSES_ROOT\Excel.XLL

More than likely you or someone you know has experienced the pain and annoyance that is when Excel crashes. There is nothing more annoying than working in your spreadsheet, making good progress, when bam! The worksheet crashes. And while there can be multiple reasons for Excel crashing on you, the most common cause is the simplest - COM add-ins.

COM add-ins are add-ins are often third party additions to Excel that add custom functionality. So if you are crashing in Excel, try disabling your COM add ins by doing the following:

In Excel 2007:

Click the Office button > Excel Options > Add-Ins on the left side of the dialog. Then click the drop-down arrow at the bottom of the dialog box and select "COM Add-Ins" from the list, then click the GO button.

In Excel 2010:

Click File > Excel Options > Add-Ins on the left side of the dialog. Then click the drop-down arrow at the bottom of the dialog box and select "COM Add-Ins" from the list, then click the GO button.

What comes up is a list of COM-Add Ins that are loaded and running. In order to stop them from running, uncheck the boxes next to the COM add-in and then click OK. Now go back to what you were doing in Excel and if the crash no longer happens, the problem was one of the COM add-ins you had loaded. You can narrow the list down by re-checking COM add-ins in the list until you find the culprit. At that point, check the third party manufacturer's website for support options. Look for an updated add-in or look in their forums to see if others have experienced the problem. You may have to live without the COM add-in that caused the problem.

Have you ever encountered this problem? When trying to pull data via a web query in Excel you get an error saying "Unable to open https://... <your site location> Cannot download the information you requested." and clicking OK dismisses the web query. As in no data. What's going on here?

What's going on is when you execute a web query, Excel will ask URLMON to download a copy of the file given byu the web URL and URLMON has navigated to the address. However, the page has a "Pragma: no-cache" header (see http://support.microsoft.com/kb/165150 and http://support.microsoft.com/kb/222064 for more information) in it, which tells WININET (which IE uses) to avoid saving the web page to the Internet Explorer cache. In order for the download operation to work, the file has to be saved to IE. Not allowing the file to be cached causes the error you see in Excel and in turn, nobody is happy.

To resolve this situation, there are a couple of things you can do:

1. Edit the web page from where you are pulling your data and remove the no-cache header. You would be looking for a meta tag like the following:

<HTTP-EQUIV="PRAGMA" CONTENT="NO-CACHE">

or

2. You can direct the client computer to set the Internet Explorer option to bypass the no-cache check for SSL sites (remember the https:// in the sample URL above). To do this, go to START and in the RUN line type REGEDIT. In the registry navigate to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings. Right click Internet Settings and left click New > DWORD Value (32-bit) and name the new value “BypassSSLNoCacheCheck” without the quotes. Double click this value and give it a value of 1.

Then try the web query again.

As a seasoned Excel user, you are no doubt familiar with Pivot Tables and how they work. (If not, here's a great place to get started on them: http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx). Perhaps you've wanted to see your pivot table data differently. We know how to pivot the tables to display our data as needed, but what if we need to see all of the data for a field in a single flat format? How can we do this?

Consider the following sample pivot table:

In order to show the data in flat file format, we need to be showing Grand Totals as we are with this table. If you aren't seeing grand totals, right click in your pivot table, left click PivotTable options. Click the 'Totals & Filters' tab and make sure both 'Show grand totals for rows' and 'Show grand totals for columns' are checked.

To display the data in a flat format, just double click the Grand total. What you get is the flat data (like the sample below) on a new tab added to your workbook.

The hotfixes for the following updates as part of the October 2011 CU have been recalled. Two critical fixes that were supposed to be part of this CU were missing. The following hotfixes have been recalled:

http://support.microsoft.com/kb/2596535

http://support.microsoft.com/kb/2596538

http://support.microsoft.com/kb/2596539

http://support.microsoft.com/kb/2596545

These updates will be be-issued as soon as possible.

Here's an interesting scenario. You have an Excel 2010 spreadsheet that contains multiple activeX controls. After you do a print preview of the spreadsheet and save the sheet, close it and re-open it you notice that some or all of the ActiveX controls are resized. They have either gotten smaller or larger, or have shifted left or right.

The fix for this problem is a two-part fix.

First, click on over to http://support.microsoft.com/kb/2596494 and click the hotfix link to request the hotfix package. Once you have the hotfix, install it.

After the hotfix is installed, you need to go into the registry and make an addition. From http://support.microsoft.com/kb/2596575:

1. Click Start, click Run, type regedit in the Open box, and then click OK.
2. Locate and then click the following registry subkey:

HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

3.On the Edit menu, point to New, and then click DWORD (32-bit) value.
4.Type MultiSheetLegacyPrint, and then press ENTER.
5. In the Details pane, right-click MultiSheetLegacyPrint, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7.Exit Registry Editor.

If you have ever used the "Track Changes" feature in Microsoft Word, then you know that the changes you make aren't deleted, but they are shown with a strikethrough and typically highlighted in red. If you have used the track changes feature of Excel, then you know that the Track Changes feature in Excel and Word function completely different. In Excel, the track changes feature highlights the cell where a change occured and makes a special comment box available that contains the data you changed and the new data. If you need to print the changes you can enable a history sheet to appear that contains your changes along with their cell references. But what do you do if you want Excel's track changes to mirror the way Word's Track Changes feature works as in the picture below? Read on.

First, you have to remember that when you enable Track Changes in Excel the workbook gets shared, and you cannot edit macros in a shared workbook. That means you will need a separate spreadsheet opened (XLSM) that contains the macro I'm about to give you. Let's say your workbook that contains this macro is called 'Book1.XLSM'. You will want to do the following:

1. Create a new workbook.

2. From the View menu, click Macros > View Macros. In the dialog that appears, click under 'Macro name' and give your macro any name you want. Then click the 'Create' button to the right. This will open the VBA editor, a new module and a subroutine with the name you supplied. The cursor is blinking between the beginning and end of the subroutine.

3. Add the following code:

Dim xLen As Integer
Dim x, y, z, addr As String

Worksheets("History").Activate
Range("G2").Select

While ActiveCell.Value <> ""
    x = ActiveCell.Offset(0, -1).Value
    y = ActiveCell.Offset(0, 1).Value
    z = ActiveCell.Offset(0, 2).Value
    addr = ActiveCell.Value
    Worksheets(x).Activate
    Range(addr).Select
    xLen = Len(z)
    ActiveCell.Value = z & " " & y
    With ActiveCell.Characters(Start:=1, Length:=xLen).Font
        .Strikethrough = True
    End With
    Worksheets("History").Activate
    ActiveCell.Offset(1, 0).Select
Wend

4. Save your workbook as a macro-enabled spreadsheet. It will have the XLSM file extension.

5. With Book1.XLSM open, also open your workbook that has your tracked changes. Go to the Review menu > Track Changes > Highlight changes and check ‘List changes on a new sheet’ and click OK. This should create a ‘history’ sheet.

6. From the View menu click Macros (by default the last item on the ribbon) > View Macros

7. Click to highlight Book1.xlsm!<Name of your macro> and click Run

The Cumulative Updates (CUs) for October 2011 have been released. Here is a breakdown of the CUs released for Excel.

• KB article 2596535 (http://support.microsoft.com/kb/2596535) - You have a Word document that contains a text box and a picture. The text box overlays the picture. When you use a computer that has the 2007 Microsoft Office suite Service Pack 2 (SP2) installed to print the Word document in Office Word 2007, the document is printed incorrectly.
• KB article 2596538 (http://support.microsoft.com/kb/2596538) - The Camera tool performs slowly in Microsoft Office Excel 2007 when the zoom level is not set to 100% in a workbook. Additionally, Office Excel 2007 runs slowly when you open the workbook or move a shape in the workbook.
• KB article 2596539 (http://support.microsoft.com/kb/2596539) -
  • You apply the hotfix package that is provided in Microsoft Knowledge Base (KB) article 2544402 or KB article 2553026.
  • You open an existing .xls file from a network location.
  • You make some changes and save the file.
  In this scenario, you receive the following error message:
  The file 'FileName.xls' may have been changed by another user since you last saved it. In that case, what do you want to do?
• KB article 2596545 (http://support.microsoft.com/kb/2596545)-

  • Consider the following scenario:
    • The  ForceShellExecute registry entry is enabled in Windows.
      
      Note The ForceShellExecuteregistry entry is in the following registry key:
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Common\Internet
    • You click a hyperlink in an Excel 2007 workbook. The hyperlink links to another workbook.
    In this scenario, the linked workbook does not open. Additionally, the pointer remains as a busy pointer.
  • Consider the following scenario:
    • You have an Excel 2007 workbook that contains a shape. The shape references another workbook.
    • You try to copy the shape to another workbook. 
    In this scenario, Excel 2007 crashes.
  • When you print an Excel workbook in Excel 2007, the data in the workbook starts to be printed on the second page of the printout. Additionally, the columns on the second page are truncated.
    
    This issue occurs when the following conditions are true:
    • The paper size is set to A4 or Letter.
    • A "fit to page" option is selected.
    • The printout is more than one page and includes the title.
    • The printer supports scaling.
  • Assume that you have a workbook on a UNC share of a server. This workbook contains a link to another workbook on the same server. For example, the link contains the following path:
    \\server\share\folder\test
    However, after you install KB2587505 and then open the workbook that contains the link in Excel 2007, the path of the link is loaded incorrectly. For example, the path that the link contains is loaded as the following:
    \\server\share\share\folder\test
    Therefore, the link is broken.
    
    For more information about KB2587505, click the following article number to view the article in the Microsoft Knowledge Base:
    2587505                                         (http://support.microsoft.com/kb/2587505/                        )        MS11-072: Vulnerabilities in Microsoft Excel could allow remote code execution: September 13, 2011
  • When you try to create a list by importing an Excel 2007 workbook to a Microsoft Office SharePoint site, you receive the following error message in Excel 2007, and the import operation fails:
    Method 'Post' of object 'IOWSPostData' failed
    This issue occurs when the following conditions are true:
  • The ADFS service is running on the computer that you use to access the SharePoint site.
  • The computer is running a 32-Bit version of Windows 7.
  • You use Windows Internet Explorer to access the SharePoint site. The Check for server certificate revocation option is enabled in Internet Explorer

On October 25, Microsoft released Microsoft Office 2007 Service Pack 3. KB Article 2591039 (http://support.microsoft.com/kb/2591039) contains technical details about SP3, while 2526086 (http://support.microsoft.com/kb/2526086) contains general information. Because Office service packs are cumulative, you do not have to install Service Pack 1 or Service Pack 2 before you install Service Pack 3. Service Pack 3 includes all fixes which were included in Service Pack 1 and Service Pack 2.

Service packs typically do not contain cumulative updates (CU) that they are released with, but they will contain previous cumulative updates. The last cumulative update was August 2011, which is included in SP3. Those installing SP3 for Office 2007 should be aware that the August Cumulative Update for Excel contained a problem that caused internal hyperlinks to be broken with the use of the ForceShellExecute registry key. This problem is fixed in the October Cumulative Update for Office 2007 which was just released. This fix is located here (http://support.microsoft.com/kb/2596545).

In addition to the two other side effects to installing MS11-072 (http://support.microsoft.com/kb/2553091) , as mentioned in the previous blog, there is yet another issue - this time with charts. Mind you this problem doesn't happen with all charts, but with some charts you will see the horizontal axis is no longer being plotted as separate data points. Before the update your chart may look normal:

But after the update your chart may suddenly look similar to the following:

There is a workaround for this problem, though not a permanent solution as yet. To work around the problem, right click the horizontal axis and click 'Format Axis'. In the dialog box that comes up you will want to set the 'Axis type' from 'Automatically select based on data' to 'Text Axis'. This will force the chart to render correctly.

Office File Validation, or OFV, was a new security feature (an add-in) released in Office 2010 and is installed as part of MS11-021, released in April 2011. It was back ported to Office 2007 and 2003. KB Article 2501584 states, "Office File Validation verifies that a particular binary file complies with the application’s expectations. Office File Validation can help prevent unknown binary file format attacks against Microsoft Office 97-2003 file formats. " (http://support.microsoft.com/kb/2501584).

However we have seen situations where, after applying MS11-021, opening Office files is significantly slower from a network location. KB Article 2570623 (http://support.microsoft.com/kb/2570623) was written for this purpose. The problem typically only affects Excel 2003, however it can affect Excel 2007 and 2010 if you have ever set the 'DisableRobustifiedUNC' registry key. If you're using Excel 2003 and are seeing an increase in time taken to open Excel files from a network location, like 40 seconds to 5 minutes, then you will want to check out KB article 2570623 (http://support.microsoft.com/kb/2570623). If you are on Office 2007 or 2010 and see an increase in time to open Excel files across the network and you can confirm that MS11-021 was recently installed, then check for the following registry key:

For Excel 2007

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

For Excel 2010

HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options

Look for a DWORD value labeled DisableRobustifiedUNC set to a value of 1. If this is set on your machine, it's recommended you delete this value or set the DWORD value to 0.

There are a couple of new problems that we are seeing on some machines after installing MS11-072: an issue with links and a problem saving files that are opened off a network.

Problem 1 - Links contain duplicate folders

We are seeing reports that after installing MS11-072 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;2553073) links are broken in Excel 2007.  On some machines with patch installed you may see duplicate folders in the URL in the 'Location' section of the edit links dialog.  The red box in the picture below shows where the hyperlink location contains two of the same folder name in the URL. That's the problem - the names weren't duplicated before the security update was installed.

While this is a known issue, at this time there is no fix for the problem and the only workaround is to manually fix the link. Again, Microsoft is aware of the issue and we are awaiting word on a hotfix. As soon as a fix is made available this blog post will be updated.

Problem 2 - Error saving file opened from a network

We are also seeing a secondary issue appear after installing this security update. Users are seeing an error after opening an Excel file from a network share and then trying to save that file. The error is "The file <name of file> may have been changed by another user since you last saved it." You are then asked if you want to save a copy or overwrite the changes.

Resolution

This problem can be resolved by installing the Excel 2007 update in http://support.microsoft.com/kb/2598032/EN-US.