Tag Archives: Excel 2007

How to add cells in Excel (screen shots of cell data added up via the SUM formula).

How to Add Cells in Excel to Sum Up Totals Automatically

Excel’s great for displaying data and even better at crunching numbers. Here’s how to add cells in Excel to sum up totals automatically… Even when you change the numbers.

A great feature that Excel has to offer is its use of formulas. Since Excel is often used to organize numerical data for a variety of operations, it can be beneficial to have an ‘addition’ function. In this guide, we are going to discuss the various ways we can add cells in Excel.

Why is the Addition Function Useful When You Need to Add Cells in Excel?

As we already … Read the rest

Excel without a Mac

I sold my 15″ MacBook Pro yesterday and am waiting on a 21.5″ iMac to arrive in 2-3 weeks. So no more adventures with Excel 2011 for a bit.

Time to dust off the Dell desktop and reacquaint myself with the “real” Excel. That of the Windows variety. I’m putting Excel 2003 in my rear view mirror (finally) and will focus on Excel 2007 and 2010.

I would love to get Excel 2013 and test it out, but the Dell will need to be replaced this year so I’ll wait until I have a new PC. I would like it … Read the rest

Horizontal Dynamic Dependent Drop Down List Example

A Dynamic Dependent Drop Down List with a Horizontal Table Reference

I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the end of the post I’ll give a link to the file I used.

Conditional Drop Down List (Excel)

Horizontal Dynamic Dependent Drop Down List Example

There are two named ranges,

    1. 1)

myCategoryH

    1. that refers to the range E1:E3 and

 

    1. 2)

myTableH

    that refers to range F1:G3.

A defined name, myItemListH, … Read the rest

How to Update a List or Range without OFFSET

I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome.

Worksheet cells that use Data Validation for a drop-down list can simplify the input process, or be used to limit the available choices. But the list needs be expandable. Here are two primary ways to keep your data validation list automatically updated, without having to resort to using the OFFSET function.

Update Your List Range with VBA

Read the rest
Analysis ToolPak AddIn

Check the Analysis ToolPak Add-In in Excel 2003 [VBA Code]

Analysis ToolPak AddInI recently began using the NETWORKDAYS function in Excel 2007, yet my file was going to be widely used by people with Excel 2003 (both are Windows versions). The NETWORKDAYS function is a standard function in Excel 2007, but with 2003 you have to install the Analysis ToolPak, otherwise you get an error.

The only way for me to avert an error with users who don’t have the Analysis TookPak installed in Excel 2003 is to use some VBA code that runs when the file is opened. I want to see if they are using a version of Excel older … Read the rest

Excel 2010 icon

An Excel Crossroads – Mac and Windows

Excel 2010 iconI bought a MacBook Pro and installed Windows 7 with Boot Camp Assistant so I could access the “normal” Excel. This was important to me at the time because I was doing Excel development work in versions 2003, 2007 and 2010 on a Windows PC.

MacBook ProI love my MacBook Pro, which I consider my computer of the future. It’s great for dealing with Photos, Movies, Music, and all of my iOS devices.

I purchased the Mac versions of Excel (2008 and 2011) but they seem like a mixture of the three Excel versions mentioned above, and can be very … Read the rest

A Dynamic Dependent Drop Down List

A Dynamic Dependent Drop Down List in Excel

A Dynamic Dependent Drop Down ListThe other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011.

In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first.

Create at Reference Table

Here’s a Table with Category names in the header row and ItemsRead the rest

Excel 2010 Clipboard Group

Ribbon Home Tab Comparison – Excel 2010 Windows and 2011 Mac

I’ll review each hard-to-find item on the Ribbon’s Home Tab in Excel 2010 (Windows) and let you know where they are located in Excel 2011 (Mac). I’ve even got some nifty pictures to speed along that understanding.

Note: The Ribbons in Excel 2010 and 2007 (Windows) are interchangeable, but I’ll only refer to 2010 below.

Each Ribbon Tab in Excel is organized by Groups. In Excel 2010 the Groups are shown at the bottom of the Ribbon. In Excel 2011 the Groups appear at the top, which I prefer.

The Excel 2010 Home Tab

The Excel 2010 Home Tab has … Read the rest

The Mini Toolbar in Excel

The Mini Toolbar is nice to have when you need to do basic formatting and the Home tab is not displayed. Introduced in Excel 2007 and available in Excel 2010, the Mini Toolbar appears above the shortcut menu when you right-click a cell, range, chart, shape or other object.

Note: There is no Mini Toolbar in Mac versions of Excel (2011, 2008).

The contents of the Mini Toolbar are dependent on the object you right-click.

Right-Click a Range

I frequently Bold and Center format my table headings, but when the Home tab is not displayed it’s quicker to use the … Read the rest

Add Freeze Sheet Panes to the Quick Access Toolbar

Add a one-click Freeze Panes command to the Quick Access Toolbar in Excel 2010 and 2007. This command icon is conveniently hidden from the Ribbon and is named Freeze Sheet Panes.

In a previous post I made the recommendation to add the Freeze Panes command to the Excel Quick Access Toolbar, but recently found out that this is a sub-optimal solution because with the Freeze Panes command this requires two clicks.

One click activates a drop-down list, then you have a choice for your second click: Freeze Panes, Freeze Top Row, and Freeze First Column. The only one of … Read the rest

Import Data From the Web in Excel

Excel 2010 and 2007 for Windows have the option to import data from the web. Excel for Mac users don’t.

An integral part of working with Excel is using keyboard shortcuts. They make your life so much easier (in the Windows versions at least, in the Mac version I think they tend to shorten your life span).

In my last post I dealt with getting a Help Topic URL, here I’m going to use the web page Keyboard shortcuts in Excel 2010 and import to a spreadsheet.

Get a Help Topic Web Page Address

As you will see, … Read the rest

Redo and Repeat Commands in Excel [Updated]

I always thought the F4 key was used to Repeat the last command, and the keyboard shortcut Ctrl+Y was used to activate the Redo command. I don’t use Redo very much, but the keyboard shortcut Ctrl+Z for the Undo command is one of my favorites. Toolbar icons for Undo and Redo command are common, yet I found there’s a toolbar icon for the Repeat command (Windows only).

QAT Undo Redo command
Adding the Repeat icon to the Quick Access Toolbar is a simple way to distinguish what commands can be repeated. The Repeat icon will be grayed-out if the current command can’t be … Read the rest

What Version of Excel am I Using?

If you’re curious about the version of Excel you’re using and don’t know how to go about it, you’ve come to the right place. It could be as simple as selecting the Help Menu and clicking About Microsoft Office Excel, but then again, maybe not. The five options below will help you sort things out.

Excel for Windows

If you’re using a Windows computer, chances are you’re using one of the three most recent versions of Excel.

Excel 2003

Select the Help menu, then click About Microsoft Office Excel.

About Excel Versions 2003

This also works with earlier versions of Excel (2002 … Read the rest

Recently Used File List in Excel

How many files will Excel show in the Recently Used File List? Well, it depends on the version of Excel you’re using and vary between 9 and ninety-nine files.

Here is a list of Excel versions, menu navigation to the recent file list, and the max files allowed.

Excel 2010, 2007
File>Options>Advanced tab>under the Display heading:
Show this number of Recent Documents: 50 max

Recent Document List Setting

Excel 2003
Tools>Options>General tab
Recently used file list: 9 max

Recent Document List Setting Excel 2003

Excel for Mac 2008, 2011
Excel>Preferences>General
Show this number of recent documents: 99 max

Recent Document List Setting Excel for Mac

A Related Feature in Excel 2010

At the bottom … Read the rest

Calculate the Xth Weekday of Any Month in Excel

I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting.

Xth Weekday of a Month

The History

It all started when my wife mentioned there were a couple of meetings she had to schedule at work, one on the second Tuesday of every month and the other on the third Tuesday of every month. That got me to thinking about how to create a formula in Excel to calculate those dates.

After getting a partial solution, other things … Read the rest