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
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
I 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
I 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.
I 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
The forerunner of the modern Excel Table is know as a List Object. Excel 2003 has this feature, and I’ll simply refer to it here as a List.
To create a List in Excel 2003, select your existing data table and choose Data > List > Create List…, or use the keyboard shortcut Ctrl+L.
Create List in Excel
To Excel will identify the List range and bring up a Create List dialog box. This box gives you the option to re-select the List range and let Excel know if the List has Headers in the first … Read the rest
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.
Select the Help menu, then click About Microsoft Office Excel.
This also works with earlier versions of Excel (2002 … Read the rest
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
Recently used file list: 9 max
Excel for Mac 2008, 2011
Show this number of recent documents: 99 max
A Related Feature in Excel 2010
At the bottom … Read the rest
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.
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
Here’s the picture I entered for the Spreadsheet Challenge.
I’ll point out the obvious and not so obvious stuff in this picture.
- Two monitors showing Excel 2007, Excel 2003, and Excel 2010 spreadsheets, and
- PDF versions of Excel 2007 Formulas, Excel 2003 Power Programming with VBA, and Excel 2010 Bible
- A MacBook Pro showing Excel 2011 and Excel 2008 spreadsheets
- An iPad showing the Documents To Go® Premium App with a spreadsheet open
And hopefully that’s going to get me a prize. 🙂 o_O
… Read the rest
There’s a neat trick you can use to copy a picture of a spreadsheet in Excel 2003 or Excel 2008 for Mac. It’s great for getting a spreadsheet snapshot into your presentation software.
First you select the range you want to copy, then hold the shift key down while you click the Edit menu.
What you see is menu items that don’t normally appear.
Select Copy Picture and you get a dialog box.
Here is a picture with the appearance As shown on screen.
And here is the appearance As … Read the rest
In a new installation of Microsoft Excel 2003 all menus are abbreviated. To see a full menu requires that you either click the expand button at the bottom of the menu or wait about 3 seconds for it to automatically expand. To put it nicely, this drives me batty.
It’s a simple thing to show full menus in Excel 2003. There are several ways to make this change, but I’ll show the easiest method for me.
- Click the Tools menu OR right-click the toolbar
- Select Customize from the pop-up menu
- Select the Options tab in the Customize box
- Under Personalized
… Read the rest