October 2010

Hide Worksheets in Excel

October 30, 2010 Intermediate
Thumbnail image for Hide Worksheets in Excel

There are some things you can do in the VBA Editor that won’t trigger the dreaded warning about opening a file with a macro. Hiding a worksheet so nobody can find it is one of those things. Excel worksheets have a visible property that can take on one of three values. xlSheetVisible xlSheetHidden xlSheetVeryHidden By [...]

Get the full story …

A New Charts Editor – Google Docs Keeps Improving

October 28, 2010 Product Review
Thumbnail image for A New Charts Editor – Google Docs Keeps Improving

Tuesday Google announced a New Charts Editor in Google Docs. This is important because Google continues to improve it’s spreadsheet offering in the cloud. And while in my opinion, Excel will always be the king of spreadsheets, Microsoft has to be aware that many younger people may very well use spreadsheets in Google Docs because [...]

Get the full story …

Absolute and Relative Cell References in Excel

October 26, 2010 Beginner

I normally don’t think absolute and relative cell references are difficult, until I try and mix them in one formula with two cell references. Come along and see what I mean. Relative Cell Reference The first example is a worksheet with data for Quantity, Price, and Total. The formula in cell C2 is Price times [...]

Get the full story …

Use the DATE Function to Find the Last Day of a Month

October 23, 2010 Formulas
Thumbnail image for Use the DATE Function to Find the Last Day of a Month

Zero has a special meaning in the Excel date system and can be used in the DATE Function to return the last day of the month. The simplest way to show this is to use a formula to find the last day of February in the coming years. With cell A2 containing the current year [...]

Get the full story …

Introduction to PowerPivot

October 21, 2010 Features
Thumbnail image for Introduction to PowerPivot

In the year 2000, I began to use Microsoft Query to pull data from corporate client’s database tables, using their raw data to create PivotTable reports. Each table required a different query, each linked by using VLOOKUP formulas. And of course the data had to be restricted to Excel’s 65,536 rows. Soon I was having [...]

Get the full story …

A Sparkline Example in Excel 2010

October 19, 2010 Features
Thumbnail image for A Sparkline Example in Excel 2010

Each week I download the over-under calories report from Lose It! and dump the data into a spreadsheet. I just created my first Sparkline graphic to show the last 7 days of this data. For this example I’ll use an OFFSET Function inside a named range, which was created in my last couple of posts. [...]

Get the full story …

Put an OFFSET Formula Inside a Named Range

October 16, 2010 Formulas

In the last post I used the OFFSET Function inside the AVERAGE Function to return the last 7 days of a range. Here I’ll create a Named Range using a formula with the OFFSET Function. I use a worksheet to inform me of the Last 7 Day Average for Over-Under Calories data, as shown below. [...]

Get the full story …

The OFFSET Function – Last 7 Data Points

October 14, 2010 Formulas

In this example I’m going to use the OFFSET Function to return a range reference to the last 7 cells in a column. Of course, it will be inside the AVERAGE Function so I can see an average for the last week. Sound complicated? Not really, it’s just an extension of something I did on [...]

Get the full story …

Switch Between Workbooks Faster in Excel 2007 and 2010

October 12, 2010 Beginner
Thumbnail image for Switch Between Workbooks Faster in Excel 2007 and 2010

In the Excel 2003 menu system, two clicks of the mouse will switch between workbooks. In the menu conflagration for Excel 2007 and 2010 it’s three clicks: View, Switch Windows, then pick the workbook. (As shown below) This doesn’t seem like a big deal, but it bugs the heck out of me. So I added [...]

Get the full story …

Become the OFFSET Function and Tell a Short Story

October 9, 2010 Formulas

The OFFSET Function is a difficult one to learn. I’ve decided to become the OFFSET function and tell a simple story of how I give you a value for the last cell in a range by using an example. The example data is a spreadsheet I use to log my over-under calories to goal each [...]

Related Posts Plugin for WordPress, Blogger...
Get the full story …

← Previous Entries