• ## Excel - General Tips

### Temperature Forecast Chart

by Published on 2013-03-21 05:04 AM     Number of Views: 22304

Some of the really cool charts that we can build in Excel involve the trick of combining multiple chart types together to make them happen. In this article, we’ll build one of those; a temperature chart that not only shows the forecasted high and low temperatures, but also the season highs and lows. The beauty of this chart is that it provides a lot of information, some of which essentially fades into the background until you really need it.
...

### VLOOKUP for Pictures

by Published on 2013-03-14 05:47 AM     Number of Views: 149935

Something that can be very handy when you’re building a dashboard is to return a certain picture depending on a condition. We can use VLOOKUP to look up data in a table and return the corresponding value from a different column, but unfortunately we can’t do that with pictures... or can we?

This example shows how to accomplish the equivlanet of a picture VLOOKUP, and is based on looking up a picture to display the appropriate icon for a weather forecast; something we use on our dashboards from our golf course. We update the weather data daily via a weather feed, and really don’t want to have to manually update ...

### Understanding How Conditional Formatting Rules Are Applied

by Published on 2013-01-22 06:30 AM     Number of Views: 35877

Conditional formatting in Excel is a powerful tool that allows you to dynamically format cells depending on the values of that or other cells’ data. In Excel 2007 the conditional formatting engine was re-written, opening things up to allow more than 3 conditional formats on any cell, as well as conditional formats that could overlap ranges. All in all, these were fantastic improvements that can lead to some very versatile and useful worksheets.

Unfortunately, the user interface to control conditional formatting is not the most intuitive. The purpose of this article is to help you understand the way Excel applies rule precedence so that you can build powerful formatting rules of your own, without getting frustrated along the way.
...

### Adding VBA Code For The First Time User

by Published on 2012-10-27 02:48 AM     Number of Views: 24390

It's always nice when you go to a forum and someone gives you a nice bit of VBA code that is supposed to accomplish your goals. But if you've never used VBA code before, it's kind of hard to know what to do with it! This article has been written to get you up and running and get that code in the right place.

Please note... this article assumes you've been directed to add your code to a standard module, as 99% of code is housed there. If your coder told you to put your code in a worksheet module or the ThisWorkbook module, this wont' quite get you there. (You should still read this article, but also this one which lists the other types of Excel modules.)
...

### Highlight Subtotals for Easy Reading

by Published on 2012-05-17 05:48 AM     Number of Views: 19404

One of the things that always struck me as odd about using subtotals is that only the words in the subtotals turn bold, and not the actual subtotals themselves. With a long list of data this can make it hard to see which numbers are the subtotals amongst the data. Fortunately this is very easy to fix using conditional formatting.
...

### Retrieving Selections From A PivotTable Slicer

by Published on 2012-05-02 07:45 AM     Number of Views: 76926

So you've built a really cool PivotTable, and you hooked up a slicer to allow exploration of the data. And now you want to do something really cool, but you need to make your formula react to the slicer value. Can you do it? Of course you can, but how?

This article will focus on the technique to do exactly that: return the value of a slicer to a formula. Note that, in order to follow along you will need Excel 2010 or higher, as Slicers didn't exist prior to this version.
...

by Published on 2012-04-04 05:36 AM     Number of Views: 14072

XLG File Tools is a FREE add-in which was created to increase the functionality of Excel and address the plethora of extra clicks that were introduced begining in Excel 2007. It's current feature set is designed to make the job of opening existing files and creating new files more efficient.

XLG File Tools is easy to use, and is guaranteed to make you more efficient. In addition, it's a snap to install and requires no administrative priviledges to do so.
...

### Sorting A Column Of PowerPivot Data By Another Column

by Published on 2012-03-28 06:16 AM     Number of Views: 41733

One of the things that used to drive me crazy about working with PivotTables in PowerPivot’s initial (2008) release was summarizing dates by month. With a standard PivotTable, we can use the built in Group functionality to group dates by Years, Quarters and Months. But in PowerPivot, that functionality wasn't implemented. To deal with this, we have to provide our own date table, but the months never really sorted well, and we had to resort to tricks to coerce them into the right order.
...

### Displaying “Last Updated” Date And Time In PowerPivot

by Published on 2012-03-28 05:49 AM     Number of Views: 29264

We use PowerPivot to display key information in dashboards, some of which can be refreshed right up to the current second. Naturally, one of the first questions asked when looking at the reports (particularly if they get printed) is “When was the data last updated?”
...

### Trigger Conditional Formats Before Printing

by Published on 2011-11-11 06:56 AM     Number of Views: 13746

My staff and spreadsheet users will tell you that any time I build a spreadsheet, there are always shaded cells on the grid. I preach that "Green means go", and make sure that any cell they enter data in has a green background. I also use blue backgrounds for "update these sometimes" cells, like tax rates. If cells are left with no colouring, though: everyone here knows that they should be left alone.

Often, I have a few data entry cells or blocks on multiple worksheets. While this follows good spreadsheet design principles, it does have a bit of a side effect: When you print the worksheets, the green backgrounds print as well. While they're great for telling the user where to input data manually, it's distracting when you are looking at a printed (presentation version) of your data or report. So the question you may be asking is what can we do about it?

We could create a full new report that essentially duplicates what we've got, but then it's both a maintenance headache and performance hit. Instead, we can use a conditional format ...

### Quick Tip – Flip Numbers From + To – (Or Vice Versa)

by Published on 2011-07-29 08:03 PM     Number of Views: 5821

I was working with some budget stuff today and wanted to forecast that a range of accounts would be written off. I had a forecast that showed the projected transactions monthly, (all zeros as the projects have been discontinued,) and the projected year-end balance. So I basically wanted to take the projected year-end balances, flip them from positive to negative and stuff them in the July transaction column. Here’s how:
...

### An Interesting Use For Slicers

by Published on 2011-04-06 07:11 PM     Number of Views: 7180

Over the past while we’ve been building a Dashboard report for our golf course. It’s got some historical information in it, but we’ve also pulled in things like weather forecasts. The intention is that our managers will be able to see where we’ve been over the past week, as well as look at the key measures that will allow us to staff appropriately for the next week.

We put out a prototype of the Dashboard report, and our Director of Golf said “This is cool. What’s the chance we could also have the events coming up over the next week listed?”
...

### Data Validation in Excel Services

by Published on 2011-03-08 08:09 PM     Number of Views: 14891

I’m a huge consumer of Data Validation in Excel. At this point, however, Excel services (the Excel webApp) is still so new that there are very few of the techniques that we normally use in Excel which are web compliant. This article looks at ways that we can implement data validation into an Excel services solution.
...

### Another Excel Services (Skydrive) Example

by Published on 2011-03-03 08:35 PM     Number of Views: 8001

This particular file will show an interactive break-even analysis for an event entailing food and beverage.

You can interact with the file and have the chart redraw. Probably the best route is to adjust by very large amounts to see the effects.

Here you go, have a play!
...

### Mapping a Network Drive to a Local Folder

by Published on 2011-02-23 08:50 PM     Number of Views: 5401

Tonight I had to do some work on my financial model from home. Naturally, I copied the files into my "work stuff" folder on my laptop hard drive, and headed out at the end of the day. But as I disclosed in my last post, it now has (shudder) external links in the file. So in order to work effectively on the model, I needed to replicate the file paths.
...

### Excel Blackjack Game

by Published on 2010-11-15 08:57 PM     Number of Views: 22628

Just for fun, I decided that I would build a game in Excel, so here's my version of Blackjack.

Features:
The rules are pretty standard Blackjack rules... adjust your bet, then hit Deal. You get two cards, the dealer gets one up, one face down. You can hit or stay, and the hand will unfold for you.
...

### Disable the Excel Compatibility Checker

by Published on 2008-09-25 05:14 AM     Number of Views: 39084

I've received several comments on my blog that people dislike Excel 2007's Compatibility Checker, and would like to turn it off globally. While it's true that you can turn it off for each workbook, the main user interface seems devoid of a way to actually tell Excel that you never want to check your workbooks for compatibility when saving to 2003 or earlier versions. ...

### Text to Speech Utility - Proof Reader for Excel 2007 (and higher)

by Published on 2008-05-06 08:52 PM     Number of Views: 12442

Introduction
This is a very simple add-in that adds a button to the Review tab's Proofing group, allowing the user to have Excel read the selected cells back to them aloud. ...

### Always Refer to the Cell Above

by Published on 2008-05-05 09:10 AM     Number of Views: 27549

Introduction
How many times have you built a table of data, and put totals on it. You ship it out the door, and another user comes along and inserts a new row right before the totals row? If you have seen this in play, you'll know that the new row is actually inserted between the end of the data range and the totals row, meaning that your sum formula no longer picks up the entire range of data!

Fortunately, the issue with missing rows in totals can easily be fixed so that a user can insert a row immediately above your totals without missing the last line(s). ...

### Have Excel Read A Range Of Data To You

by Published on 2008-05-04 09:08 PM     Number of Views: 11832

Introduction
When keying in large amounts of data, it can be helpful to have Excel read the input back to you... either as you go, or after you have finished. In either case, this lets you focus on reading the numbers from the page, comparing what you have typed to the original values.

Page 1 of 2 1 2 Last
• ### Recent Forum Posts

#### Generate Combinations from List

I got a method? Mom would be proud...

horseyride Yesterday, 09:46 PM

#### Generate Combinations from List

Or alternative to horseyride method ;-)
Leave LISTCOUNTRY as is.
TBLCOUNTRY code below:

Code:
```let
Fonte = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],```
...

Bill Szysz Yesterday, 09:26 PM

#### Workbook_BeforeClose not working when closing application

In Excel 2010 if I use the close workbook "X", Workbook_BeforeClose works, but if I use the application close button, it does not - why?...

rdwray Yesterday, 04:43 PM

#### Generate Combinations from List

The key is to add an index before self merging, then use a column to filter out index2...

horseyride Yesterday, 01:29 PM

#### drop down list returning list

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your...

navic Yesterday, 12:47 PM