MrExcel Publishing
Your One Stop for Excel Tips & Solutions

TechTV


With the new year coming up, you might want to track progress towards some sort of a goal. This cool chart in Excel contains elements that indicate if you are on track to meet a goal.

This article helps to understanding pivot table mouse cursors.

Sudoku is a 9x9 grid. In the newspaper each day, they may fill in 20-30% of the 81 numbers in the grid. Your goal is to fill in the remaining numbers.

Do you use Finance.Yahoo.com to look up stock prices throughout the day? That web page even has a link that will allow you to download the data to a spreadsheet.

Excel offers 65,536 rows of data on a single spreadsheet. What happens when you have more data than this? Many people turn to Access – but the great news is that you can use that Access data and still do Excel Pivot Tables on the data to produce quick summary reports.

Are you responsible for managing a sales force? Build an Employee-Of-The-Day report to recognize and reward the best sales reps.

We’ve shown a lot of examples with Pivot Tables. There is a related technology called Pivot Charting in Excel. With a Pivot Chart, you can build a dynamic chart that is very easy to query and change using a few dropdown selections.

Many manager’s eyes glaze over when they are presented with a report full of numbers. If your Excel data has any geographic component, you can use Microsoft MapPoint to plot the data on a map. MapPoint is a separate program that you can buy from Microsoft.

Pivot Tables are Excel's most powerful feature. Other tips at this site discuss pivot tables, but today's show talks about two interesting uses for Pivot Tables. First, you can use a pivot table to come up with a unique list of values from a field. Second, you can use a pivot table to show the percentage of items in a row.

You have a dataset showing a list of manufacturing defects from the past year of data. There are over 11,000 rows of data. The columns show the date of the defect, the assembly line, and the type of defect. With 11,000 rows of defects, you want to do something to improve the quality, but where do you start?

Many times, we are building spreadsheets for other people to fill out. If those other people don’t watch Call for Help, they may not be as spreadsheet savvy as you. These people might find unusual ways to enter the wrong type of data – entering text where you expect numbers, or spelling regions the wrong way. Use Excel’s data validation features to control what can be entered in your spreadsheet.

It turns out that Excel has a cool feature where you can highlight a range of cells and have Excel speak the values in those cells. This will allow you to listen to the numbers while you compare them to the printed paper. When you involve two senses (hearing and sight), you will engage more of your brain and the process will be less tedious.

You can easily keep scores with Excel. This article shows how to keep score of a baseball game, cards and darts.

Excel VLOOKUP »

06/03/2005

A lot of people try to use Excel as a database. While it can work as a database, some of the tasks that would be very easy in a database program are fairly complex in Excel. One of these tasks is matching two lists based on a common field; this can be easily accomplished using Excel VLOOKUP.

Sorting in Excel »

06/02/2005

Excel is great at putting data in order. There are some cool tricks that allow you to sort perfectly every time.

Excel has a number of great tools for playing the lottery. You can use Excel to generate random numbers, to analyze past numbers to see if any numbers are "hot". You can also use Excel's functions to determine the odds of winning.

When you were in math class, did you ever have to make drawings on graph paper? I remember drawing a drawing of my home to scale using the paper that had a light blue grid on the white paper. Each box on the graph paper would represent a half meter in real life.

Here is a cool technique for creating a thermometer style chart in Excel.

The drawing toolbar offers some amazing business diagrams for use with Excel. To display the drawing toolbar, select View - Toolbars - Drawing.

This article shows how to add pop-up pictures to your spreadsheet.