Meet Our Authors
- Bob S
- Dee Anne Kotzer
- Gabe Y
- Gerry V
- Jane H
- Jeff Tincher
- Kelly Kilpatrick
- Marge R
- Mike G
- Neall A
- Peggy S
- Sheri T
More Counting in Excel
By Jane H
Last year I wrote a blog about using the COUNT and COUNTA functions in Excel, but what if you wanted to count the number of cells within a range that meet a single criterion that you specify. For example, I want to know how many OFF2007NF classes we’ve scheduled for this year (see following image).
In this instance, I will use the COUNTIF function which has two arguments. The Range of non-blank cells you want to count and the Criteria which determines which cells will be counted (see following image). The Criteria is not case sensitive.
Some possible valid Criteria values follow:
- text string or a number (i.e. “off2007nf”)
- a cellular reference to that value (i.e. B4)
- wild cards (i.e. “*2007*”)
Note: There is a new enhanced function in Microsoft Excel 2007, called COUNTIFS, which works like COUNTIF only you can define multiple criteria.
Filed Under Excel |
Customize Excel Comments
By Jane H
If you know how to create comments, did you know that you can change that drab yellow box to a more exciting, eye-catching shape? Ok, this is easy to do in Excel 2003 and is a great way to catch people’s attention. After you insert a comment on an Excel cell, select the border of the comment (Notice the pointer has a four-header when I clicked the border).
Filed Under Excel |
Custom Order Sort in Excel
By Jane H
Sorting is a popular and easy feature in Excel. You simply select a cell in the column you want sorted and the click Sort button to sort in ascending or descending order. But what if you wanted an exception to the normal sort rules? In the following image the list is sorted by last name. Rosa Garcia is my best client and I want her to always be on top of the list when I sort by last name. This article will show you how a custom order sort easily achieves that result (Click image for larger view).
Filed Under Excel |
Benefits of Training
By Sheri T
People are either thrilled or upset when their company tells them they have to attend a training. I do understand that when you are already very busy, it just makes your day more stressful to be away from your desk for several hours. BUT, when you take the time to learn the most efficient way of performing task, you will be more efficient. Training.com states that you will actually be 230% more effective than someone who has not been trained. This means that the few hours you give up in a training class, you will make up for in efficiency multiple times over!
In today’s economy the best thing you can do for your career is to be up-to-date with the latest technology as well as being an efficient user of that technology.
Don’t consider training a burden on your day. Consider it a benefit, one that no one can take from you (even if you do have to change jobs). It is reassuring that your company is spending their money investing in your skills.
Filed Under Desktop Education, Excel, General, Microsoft Access, Office, Outlook, PowerPoint, Visio, Word |
Pie charts are great for data that is arranged in one column. Pie charts show the size of items, proportional to the sum of the items. The data points in a pie chart are percentages of the whole pie. But what do you do when some of your values are so small that they display as slivers of the pie (see following image)? It’s really difficult to evaluate kumquats, kiwis, and cherries in this traditional pie.
Filed Under Excel |
A while back a client was having some problems with an imported text file in Excel. There were date columns that he needed to be formatted as dates so that he could calculate with them (i.e. # of days elapsed between two dates). In Excel, he tried a Text to Columns and change the date columns to dates, but the date displays as a series of ###.
Filed Under Excel |
Copy and Paste Techniques for Excel
By Jane H
Most people know how to do a basic copy and paste procedure within the same Microsoft application. But did you know that when you copy cells of data from Excel, the way you paste it into Word or PowerPoint will produce different effects?
In both the 2003 and 2007 versions of Microsoft Office, you will find not only a simple copy/Paste function, but the ability to paste Excel information into Word or PowerPoint as a Link or a Picture! When you paste the Excel information as a link, changes to the data will be reflected on the Word or PowerPoint file. Pasting information from Excel as a pictures ensures that no one changes the data on the Word or PowerPoint file. Read more
Filed Under Excel, PowerPoint, Word |
If you have ever collected customer information in an Excel 2003 workbook and wished you could transfer it to an Outlook contacts folders, the good news is that it is relatively simple to bring into Outlook 2003. You should start by examining the Excel data and making sure it is arranged into a proper list. Read more
Excel text to columns
By Jane H
At neat feature in Excel that is underutilized is text to columns. This feature allows you to divide a column of text into multiple text columns. For example, suppose you had a column with both first and last name. You cannot sort by last name when the data is configured like that (see following image).
Column B has both first name and last name
Filed Under Excel |
Keyboard Shortcuts in MS Office
By Sheri T
Here are some of my favorite shortcuts. If you feel like you can’t find any of your commonly used features now that you have MS Office 2007, all the keyboard shortcuts have remained the same.
Feel free to add your favorites to the list!
All of MS Office
| Ctrl-Home |
Move to the beginning of the file |
| Ctrl-End |
Move to the end of the file |
| Ctrl-x |
Cut |
| Ctrl-c |
Copy |
| Ctrl-v |
Paste |
| Ctrl-p |
Print |
| Ctrl-a |
Select All |
Excel
| F11 |
Charts the selected cells |
| Ctrl- + |
Inserts a new row or column |
| Ctrl- - |
Removes the selected row or column |
| Ctrl- * |
Select an entire block of information |
| Ctrl-PgDn |
Goes to the next sheet in the workbook |
| Ctrl-PgUp |
Goes to the previous sheet in the workbook |
Word
| Ctrl-Enter |
Inserts a page break |
| Shift-Enter |
When using bullets or numbering to stay at same point |
PowerPoint
| F5 |
Run the presentation from Slide 1 |
| Shift-F5 |
Run the presentation for the current slide |
| When running the presentation, type the number of the slide and press Enter to go to that slide |
Filed Under Excel, General, Office, PowerPoint, Word |
