Counting in Excel

There are three simple COUNT functions if you need to count cells in Microsoft Excel. There are two additional COUNT functions (COUNTIF and COUNTIFS) that are a little more complex and require multiple arguments. For this post, I'll only focus on COUNT, COUNTA, and COUNTBLANK.

=COUNT(value1,...)
This function counts the number of cells in a range that contain numbers. If the cell contains any alpha character, it will not be counted. The arguments can be a single cell, range of cells, or a non-contiguous range of cells (multiple arguments).

=COUNTA(value1,...)
This function counts the number of cells in a range that are not empty. Think of the A as ALL. I want a count of ALL cells that contain something. The "something" can be any character, as long as the cell is not empty. The arguments can be a single cell, range of cells, or a non-contiguous range of cells (multiple arguments).

=COUNTBLANK(range)
This function counts the number of empty cells in a specified range of cells. If someone used the SPACEBAR to "clear" a cell, this function will recognize the space as a character and count it. It truly means the cell has nothing entered in it.

Move Text in Word with F2

Shortcuts are wonderful. I've used CTRL+X and CTRL+V for a simple cut and paste, but have you used the function key F2?

1. Select the text to move.
2. Press the F2 function key on your keyboard.
3. Place the cursor (insertion point) where you want to move the text to and then press ENTER.

Hiatus is over

My apologies for letting this blog become neglected for the past couple of years. I've have a lot of tough personal issues with myself and family. I finally feel like I'm in a place to resume posts. The next new post will be in March 2017.

Capture notes and info from whiteboards and more with Office Lens

The other day, one of our student workers and I were in a meeting and he was taking notes "old school" on paper. When we were done, he wanted to share his notes with me, so he took out his phone and took a picture. I was concerned since it was a horrible angle and the light from the window was reflecting on the paper. The picture of the notes he sent was auto corrected, angle adjusted and perfectly readable. WHAT?! I had to know what it was. He told me Office Lens. Best of all, it's free. There it is. My favorite adjective:  FREE. Tell me more.

Office Lens from Microsoft is available for free for Windows phone, iOS andAndroid. It will allows you to take a picture of a photo, document or whiteboard and then save it where you want. When taking the picture, it will work to frame the subject as you move. When you have the subject framed, take the picture. It's not perfect, but I've found it far better than others. After taking the photo, you have the opportunity to crop even more if needed. When you're ready, click the Save icon at the bottom. You'll be asked to name the image and where you'd like to save it. You can save it on your phone, or if you want to save it in OneNote or OneDrive you'll need to sign into your Microsoft account if this is the first time.

I love the option to save to OneNote since I use OneNote for taking notes in meetings and at conferences. I often take a picture of a flip chart or presentation slide since a picture is better than retyping everything. I used to send pictures to OneNote on my phone, but the angle or lighting can be weird and make it hard to read. Office Lens does a great job with the lighting and angle. Also, OneNote has the option to make the text in an image searchable. Just right-click the image in OneNote and click Make Text in Image Searchable. SWEET!

Now for the big questions, "How can I use this in training?" Easy answer. If you ever do brainstorming sessions or group breakouts, you can use Office Lens to capture the information quickly without retyping everything. HUGE time-saver. Plus, it's so easy to share from your phone or tablet.

Below are two photos. The first is the picture I took from my phone's camera. The second I took from the same distance, same lighting and same angle using Office Lens. Notice the lighting and angle adjustment to make it more readable? Yeah, it's pretty nice.

Happy St. Patrick's Day!

Saint Patrick was a gentleman,
Who through strategy and stealth,
Drove all the snakes from Ireland,
Here’s a toasting to his health.

But not too many toastings
Lest you lose yourself and then
Forget the good Saint Patrick
And see all those snakes again.

'Beannachtam na Feile Padraig!'
Happy St. Patrick's Day!

List of all sheets in an Excel workbook

I love when tips work in Windows and on the Mac. Here's one that has been around for many versions, but still a favorite of mine. Right-click any of the worksheet navigational arrows (bottom-left) and a list of all sheets are listed. Simply click the one you want and you'll activate that worksheet. Much easier than clicking the Next or Previous arrow a bunch to find the sheet you need.

Happy Holidays!

Happy Birthday, Windows!

Interface Manager is the code name and is considered as the final name, but Windows prevails because it best describes the boxes or computing “windows” that are fundamental to the new system. Windows is announced in 1983, but it takes a while to develop. Skeptics called it “vaporware.”

The fully-packaged Windows 1.0

On November 20, 1985, two years after the initial announcement, Microsoft ships Windows 1.0. Now, rather than typing MS‑DOS commands, you just move a mouse to point and click your way through screens, or “windows.” Bill Gates says, “It is unique software designed for the serious PC user…” 

Happy Birthday, Access!

Microsoft released Access version 1.0 on 13 November 1992. Access 1.1 was released in May 1993 to improve compatibility with other Microsoft products and to include the Access Basic programming language.
I started using Access with version 2.0. It is a tough program to use as it borders between end-user and programmer. Both users can use it at different levels, which makes it great.
I giggle every time I have a question that I know Access can do, but I don't know how to make it work. I ask a programmer friend of mine, and the first thing they have me do is switch to SQL view to work. I say, "that's great, but how can I use the GUI instead?" Their response is, "I don't know." Two users, two very different experiences with the same product.

PivotTables in Excel 2013

I love PivotTables. They are an easy way to take the data you have and manipulate in such a variety of ways to get the results you want. Today I am being efficient and sharing a wonderful post from the Excel Blog all about PivotTables. Seriously, I can't do any better than this... Practical PivotCharts in Excel.

Sort Folders in Outlook 2013

In previous versions of Outlook, your folders were sorted alphabetically. What annoyed me is that they were sorted among the Drafts folder, Sent items, etc. To work around this, I either put all folders under my Inbox or in a special folder I labeled "My Folders."

With Outlook 2013, you can move your folders anywhere you want by clicking and dragging. You'll notice when you move a folder, a gray highlight bar appears to let you know where it is going. Watch the dark gray horizontal line to determine it will drop into another folder, be a main or a subfolder.

If you really do prefer the alphabetical sorting, right-click your e-mail at the top of the list and click Sort Subfolders A to Z.

Happy Spreadsheet Day!

October 17th was voted the best day for Spreadsheet Day, because VisiCalc, the first spreadsheet for personal computers, was released on October 17th, 1979.
If you're a spreadsheet geek and like to read, you can learn all about the history of spreadsheets at A Brief History of Spreadsheets, by D.J. Power.

Happy Birthday, Word!

In 1981, Microsoft hired Charles Simonyi and Richard Brodie, former Xerox programmers, and the primary developers of Bravo, the first GUI word processor, They started work on a word processor called Multi-Tool Word for MS-DOS in 1983. Its name was soon simplified to Microsoft Word. Free demonstration copies of the application were bundled with the November 1983 issue of PC World, making it the first program to be distributed on-disk with a magazine. Unlike most MS-DOS programs at the time, Microsoft Word was designed to be used with a mouse, and it was able to display some formatting, such as bold, italic, and underlined text, although it could not render fonts.
This takes me back to high school when I learned to type on WordPerfect 4.0 for DOS. Can anyone tell me what F7 did? It's been years, but I still remember it is to print. 

Happy Birthday, Excel!

Microsoft released the first version of Excel for the Macintosh on 30 September 1985. Excel was one of the first spreadsheets to use a graphical interface with pull down menus and a point and click capability using a mouse pointing device. The Excel spreadsheet with a graphical user interface was easier for most people to use than the command line interface of DOS spreadsheet products, like Lotus 1-2-3. Many people bought Apple Macintoshes so that they could use the Microsoft Excel spreadsheet program. When Microsoft launched the Windows operating system in 1987, Excel 2.0 was one of the first application products released for it.

Get "Click Happy"

"Click Happy" is a term I use with people to describe my unconscious clicking of everything. I see something, and I must click on it. Sometimes once, other times twice or more. I get this from my use of clicking to select things in Microsoft Word. There are a bunch of shortcuts to select in Word, and most options work in many other application programs as well. Here they are, and happy clicking!

To select this	Click this
A character	Drag across the letter
A word	Double-click
A line	Move the pointer to the left of the line until you see the left-pointing arrow, and then click
A sentence	CTRL–click anywhere in the sentence
A paragraph	Triple-click the paragraph OR Move the pointer to the left of the paragraph until you see the left-pointing arrow and double-click
Multiple paragraphs	Move the pointer to the left of the paragraphs until you see the left-pointing arrow, and then click and drag up or down
A large block of text	Click at the start of the selection, scroll to the end of the selection, and  SHIFT-click
Entire document	Move the pointer to the left of any of the document text until you see the left-pointing arrow, and then triple-click
A vertical block of text	Hold  ALT , and then drag over the text