During today’s Professional Development Conference keynote, Ray Ozzie made an exciting announcement about a new way to view, edit, and collaborate with Excel. If you weren’t able to watch the keynote, feel free to check out the following links: PDC Keynote Video , Official MS Press Release , and Channel 9 Video . In case you’re in a hurry, here’s the bottom line: Excel is providing a web application which allows you to view, edit, and do real-time collaboration on spreadsheets in your choice of web...

Today's author, Dan Battagin, a Program Manager on the Excel team, talks about joining two tables in Excel (a.k.a. returning multiple rows for VLOOKUP). Today, we'll take a look at the VLOOKUP function, and work out a way to get around its major drawback - it returns only a single value that matches the lookup. If you're like me, there are times when you have a big table of data which is pretty well normalized, and you want to pull all of the rows out of that table that match a certain criteria....

Today's author, Stephane Viot, a Program Manager in the Office Global Experience Platform team, reflects on how leveraging application features could help speed up the development of your VBA solutions, while improving their performance. Often -but hopefully not always- VBA developers forget about the "A" in "VBA". And by that I mean they might have the tendency of trying to do everything in VBA, like they would in VB, forgetting about what the host application has to offer. To illustrate that point...

Today's author, Christian Stich, a Program Manager on the Excel Services team, discusses how to enable users to quickly and easily create workbooks using external data connections specified in server based data connection files. Overview Excel and Excel Services support importing external data, which can be specified using connections embedded in the workbook itself or in Office Data Connection (.odc) files. One of the advantages of using .odc files is that any updates to the external data connection...

Today's author, Dany Hoter, a Product Planner on the Excel team, talks about some performance characteristics he recently discovered using various methods to manipulate large ranges using VBA. Problem description You have a large range in Excel with data. Let's say it contains 100,000 rows and 50 columns for each row (Yes you are using Excel 2007 of course). So altogether you have 5,000,000 cells. Columns A to F have some alphanumeric data that you need analyze and based on the combination of values...

Today's author, Charlie Ellis, continues discussing the spreadsheet he built to solve Sudoku puzzles. In my previous post , I walked through a number of formulas for setting up the valid values and solution board. In this post we'll cover using iteration and other formula tricks to help solve the puzzle. Using valid values to drive solutions Looking at cells P22:R24 below, we can clearly see that the only solution here is 7: Let's reflect this in the solution board. We're going to modify the "else...