Excel User Group

What's the dif? Gridlines vs borders in spreadsheets

Excel Blog — Wed, 23 May 2012 16:58:00 GMT

A lot of you want to know the difference between gridlines and borders. Gridlines help you work in a spreadsheet, and borders help you highlight important information in one for your audience. Gridlines appear automatically so you can see how your data is organized into rows and columns; borders need to be added by you so you can highlight certain cells. This post describes how to work with both of them. ...( read more ) Read More......(read more)

Split and recombine data in URLs

Excel Blog — Wed, 16 May 2012 21:43:00 GMT

Issue: I have a list of URLs with their corresponding page views. The URLs include a section of the site and the date when the page was published. I'd like to calculate the total number of page views by site section and the total number of page views by publication date. I want to reduce the time it would take to manually enter this data into new columns. To save time, I plan on using the Text to Columns wizard and Concatenate function rather than a complex formula. Once you learn the steps,...(read more)

Quick Trick: Resizing column widths in pivot tables

Excel Blog — Tue, 08 May 2012 21:56:00 GMT

Who could be better than a Business Intelligence analyst at teaching us a thing or two about Excel? We asked our own number-crunching wizard Stacey Armstrong to share some Excel tricks she's learned along the way. In this first one, she shows us how to change the default width of a column in a pivot table. Knowing this is especially helpful when you're working with data that that makes a row really long. ...( read more ) Read More......(read more)

Updated! Try it for free: Count values that meet a condition with the COUNTIF function

Excel Blog — Fri, 04 May 2012 16:39:00 GMT

Update: See near the bottom of this post for the latest Office how-tos on embedding Excel and PowerPoint files on web pages. You probably know how to use the COUNT function to count cells that contain a value. But what if you want to count only the cells that meet a condition, such as being greater than or equal to a number or date you specify, or that matches text? That's where the COUNTIF function comes in really handy. Keep reading to learn more, and try out this great function for free using...(read more)

Using multiple criteria in Excel Lookup formulas

Excel Blog — Fri, 27 Apr 2012 01:01:00 GMT

If you want to look up a value in a table using one criteria, it's simple. You can use a plain VLOOKUP formula. But if you want to use more than one criteria, what can you do? There are lots of ways using several Excel functions such as VLOOKUP, LOOKUP, MATCH, INDEX, etc. In this blog post, I'll show you a few of those ways. ...( read more ) Read More......(read more)

And the winner of the VLOOKUP Great White Shark Award is...

Excel Blog — Wed, 11 Apr 2012 21:13:00 GMT

JP Pinto! Congratulations! His post was one of seven chosen by Bill Jelen as the most innovative ones published during VLOOKUP Week . He won a copy Bill Jelens book Microsoft Excel 2010 In Depth. You can find links to all seven posts and see the voting results in our wrap-up post of VLOOKUP Week . ...( read more ) Read More......(read more)

Excel: not just for geeks

Excel Blog — Fri, 06 Apr 2012 16:46:00 GMT

Ugh. Excel. It's not as intuitive as a Word doc, and not as aesthetically pleasing as a PowerPoint presentation. If you're more creative than analytical, you might feel as though it's dry and boring and filled with numbers you'd rather not look at. However, I can tell you that after you familiarize yourself with it, it is the best tool Microsoft Office has - and extremely easy to use! This guest post is written by Divya Bahl, a blogger for the popular site Her Campus . ...( read more...(read more)

VLOOKUP Week Wraps Up: Vote For Your Favorite Post

Excel Blog — Wed, 04 Apr 2012 19:56:00 GMT

VLOOKUP Week has ended. The brainchild of Excel MVP Bill Jelen, the idea inspired all things VLOOKUP, including VLOOKUP odes, haikus, vampires, and a way to track shark attacks. Setting all fun aside--Excel experts created a crazy number of useful and innovative applications of VLOOKUP in seven days. The week might be over, but thanks to the enthusiasm of the Excel community and to the Web, the information will continue to instruct Excel users at all levels of expertise. So thanks, Bill, and thanks...(read more)

New SQL Server 2012 Tools for MS Excel 2010

VSTO & .NET & Excel — Sat, 31 Mar 2012 22:48:09 GMT

Microsoft has recently released a new version of their toolkit Data Mining for MS Excel. It’s for SQL Server 2012 and explicit target MS Excel 2010. To download it click on the following text: Microsoft SQL Server 2012 Data Mining Add-ins for Microsoft Excel 2010 In addition, Microsoft has released a new version of their PowerPivot tool [...] Read More......(read more)

VLOOKUP Week; Using VLOOKUP in VB

VSTO & .NET & Excel — Thu, 29 Mar 2012 13:49:45 GMT

Bill Jelen, MrExcel, has marked this week to honor the VLOOKUP function in MS Excel. Around the online Excel development community You will find contributions, usually blog posts, that gives various aspects for using or not VLOOKUP. I thought it would be interesting to create a case and see how VLOOKUP can be part of [...] Read More......(read more)

VLOOKUP Tutorial: Updating prices in a master product list

Excel Blog — Wed, 28 Mar 2012 20:39:00 GMT

In honor of VLOOKUP Week, MVP Bill Jelen has created a VLOOKUP tutorial for those of you who have a basic working knowledge of Excel but want to improve your skills. You most likely know the core set of functions that come with Excel and have heard that VLOOKUP is an easy way to look up a value in an Excel spreadsheet, speeding up getting work done. But that's about it. Bill's tutorial starts there. ...( read more ) Read More......(read more)

Today's Giveaway: Odes to VLOOKUP (It ain't over until the formulas stop signing)

Excel Blog — Tue, 27 Mar 2012 19:15:00 GMT

Bill Jellen found it. Erica Rhein started it. She posted a comment on Facebook declaring her complete dependence on VLOOKUP. "I use Pivot Tables and VLOOKUP every day. I would be screwed at my job without it." To Bill's formula-possessed mind this statement equals a love poem. So he decided today's VLOOKUP Week door prize would be the best Ode to VLOOKUP. Check out the haikus and poems about undying VLOOKUP love already submitted, then or add your own by entering it as a comment...(read more)

Tuesday’s free Webinar: Simplifying your data in Excel

Excel Blog — Mon, 26 Mar 2012 16:57:00 GMT

Join Tuesday's free Office 15-Minute Webinar to learn about simpliying data in Excel. You can join the webinar live with the link below at 9:15 am PDT or go to http://aka.ms/offweb for complete information on our webinars. Join online meeting https://join.microsoft.com/meet/dougt/F274WBQZ The best option is to download the free Lync Attendee for sound/video, or you can use your browser and call in for sound: 1 (888) 320-3585. Conference ID: 84172528 What you will learn at Tuesday’s webinar...(read more)

Ride the Shark! It's VLOOKUP Week March 25-31, 2012

Excel Blog — Thu, 22 Mar 2012 22:39:00 GMT

What strikes terror into the hearts of Excel beginners? VLOOKUP! Excel power users, though, can't live without it. Excel MVP Bill Jelen has declared March 25 - 31, 2012 to be VLOOKUP week . He's invited his fellow Excel experts to publish VLOOKUP posts on their sites during the week. Already it's taken on a life of its own with VLOOKUP t-shirts and coffee mugs, and a theme song and logo. ...( read more ) Read More......(read more)

What's your favorite thing about Excel Pivot Tables?

Excel Blog — Wed, 21 Mar 2012 01:43:00 GMT

Do you belong to LinkedIn ? Did you know that LinkedIn has lots of Excel user groups where Excel trainers, developers, financial modelers, and even Excel blackbelts compete to out geek each other--or just share information. (The Excel Blackbelt group is for data visualization experts.) ...( read more ) Read More......(read more)

What's your favorite thing about Excel PivotTables?

Excel Blog — Wed, 21 Mar 2012 01:43:00 GMT

Remove Duplicates in VB

VSTO & .NET & Excel — Mon, 19 Mar 2012 13:42:49 GMT

I have some articles in the pipeline that shows how to achieve various tasks in MS Excel with VB.NET. It’s no rocket science but practical solutions for frequent tasks. The first article is this that covers how to remove duplicates from tables. To following screen shot shows the table before removing any duplicates: In the [...] Read More......(read more)

Freeze Panes made simple (Video)

Excel Blog — Wed, 14 Mar 2012 23:50:00 GMT

Ever wanted to see just a part of your Excel worksheet that's way over on the right? You scroll over and find the information you're looking for, but your row or column headings-sometimes both-have disappeared. Or maybe you want to see data in one row that's at the bottom of your worksheet (which might contain hundreds or even thousands of rows). How can you go to that row and still see how its data has been categorized in the headings? By using the Freeze Panes commands on the View tab...(read more)

Samples for learning conditional formatting: icons, formulas, cell values, and more

Excel Blog — Mon, 12 Mar 2012 20:52:00 GMT

When we released Excel 2010, we published a workbook for you to download, which contains samples of conditional formatting rules. The samples are really helpful for learning how to track trends, check status, spot data, and find top values. Some samples are for busineses and some just for fun. You can find out how to compare bicycles by using a rating scale; calculate sales for a specific region and target; and more. ...( read more ) Read More......(read more)

Tip: Add a watermark to an Excel worksheet

Excel Blog — Thu, 08 Mar 2012 18:50:00 GMT

If you're collaborating or creating sensitive material in Excel, you probably need either a DRAFT or CONFIDENTIAL watermark. Excel doesn't come with a built-in way to add a watermark, but there's a pretty painless way to do so. We show you how and give you sample watermarks to get you started. ...( read more ) Read More......(read more)

Add-in Express vs Visual Studio Tools for Office (VSTO)

VSTO & .NET & Excel — Thu, 01 Mar 2012 22:15:03 GMT

I recently stoped by at Add-in Express and noticed an interesting series of articles that compare their RAD-tool with VSTO. You may read themselves at the following URLs: Add-in Express vs. Visual Studio Tools for Office Add-in Express vs. VSTO: Version neutrality for your Office extensions Add-in Express vs. VSTO: Visual designers for Office Ribbon [...] Read More......(read more)

Circular References: Excel's most popular (!#?%) error message explained

Excel Blog — Fri, 17 Feb 2012 23:04:00 GMT

Millions of people using Excel don't get why they see the "circular reference" error message right after they've entered a formula. The message means that your formula is trying to calculate its own cell--kind of like when a dog chases its own tail. Because so many of you (millions) searched on "circular reference" on Office.com, we thought we should very clearly explain how to remove or fix your formula. ...( read more ) Read More......(read more)

Simplifying data entry with a list box and VBA code

Excel Blog — Tue, 14 Feb 2012 20:00:00 GMT

We show you how we used an ActiveX list box control and some VBA code to allow our users to enter multiple choices in cells. ...( read more ) Read More......(read more)

From VBA to VB.NET using ExcelDna

VSTO & .NET & Excel — Mon, 13 Feb 2012 23:25:01 GMT

This is a notification about a recent published article by Patrick O’Beirne on his blog. Patrick has compiled a list of resources, for native Excel developers, to develop User Defined Functions (UDFs) with ExcelDna in VB.NET. For some years ago several developers in the online Excel community made quite often critical comments and rant a lot about using .NET [...] Read More......(read more)

The Great Microsoft Office Portal

VSTO & .NET & Excel — Sat, 04 Feb 2012 14:46:04 GMT

As most of us already know, Microsoft has for the last couple of years built up an enourmus giant knowledge base about Microsoft Office on the internet. As a consequence it has also become more difficult to navigate around and find the wanted information we are looking for. However, today I discovered the Great Portal to Microsoft [...] Read More......(read more)