Automating Symbols in Office

Everyone gets tired of typing the same thing over and over again. But there is nothing worse, than having to type symbols repetitively! For example the 'ñ'. Now I know some of you know the 4-key shortcuts for those symbols, but the rest of us usually struggle with them.
However, you can make that task much easier by either using a shortcut key or Microsoft's AutoCorrect feature.

First find the symbol

Insert Tab > Symbols Group > Symbol Button

Click on More Symbols

Click on the Font drop down box and find your default font; in this example I’m using Times New Roman

Scroll down the letters until you find the symbol you want to use; in this example I’m using the ñ

Choosing Your Shortcut

You then have 2 options:

Shortcut Key

Type the keys you want to use as a shortcut.

IMPORTANT!! DO NOT USE THE CTRL KEY! That key is already used with the Office standard shortcuts. Use the ALT key instead.

Click the Assign button

Click the OK button

AutoCorrect

The symbol should already be showing in the With box

Click in the Replace box and type what you want your ‘trigger’ to be.

That's it! Now all you have to do is either type your shortcut key of your AutoCorrect trigger! 

Work Smarter Not Harder!!

Word vs Excel

People have a tendancy to use the software they feel most comfortable with. And I completely get that.
But...
Sometimes we make it harder on ourselve by trying to use a butter knife instead of an actual screwdriver.

Once of my students had a roster done in word. She had two issues.

1. Sorting her clients
2. Creating totals by type

First off let me just say you CAN sort and you CAN do formulas in Word. The formulas are pretty limited though. I'll post on that later. 
The tip to which software on this particular project is the totals. She needed to see how many clients were Current, Pending, New and Cancelled.
This is the original Word page. (Names have been changed)

My student was manually adding up her totals.
I took her list, a simple Copy and Paste, and put it into Excel for her.

I split her names up, Text to Columns feature in Excel, and added her totals at the top using the COUNTIF function.

This is how the COUNTIF function works:
=COUNTIF(range,criteria)
Think about it like this, 
what do I want to find (criteria)
where is that data located (range)
I listed all the types of status she wanted to track at the top, then put my COUNTIF beside it.
My criteria for Current became cell C3
And my range was the entire column of G
=COUNTIF(G:G,C2)

Sometimes you should just go find a screwdrive and leave the knives for cutting steak!
Blog ACME Clients.xls

Viewing 2 Sheets at the Same Time in the Same Excel Workbook

Hello,

I took several classes with you at Cal Poly and the last was the PPT class.

I have an Excel question. Is it possible to see two worksheets from the same workbook at the same time? If so, how do I do this?

Thanks,

Kathy

Hi Kathy,

Yes it is.

First you will need to create a 'new window', basically this creates another window with the same workbook in it.

Go to your View Tab and click on New Window. 

You should see two references to the workbook Name; 1, Name; 2.

Now to arrange them side by side:

Click on View again and click Arrange. I recommend Tiled as the choice.

 

You should now be able to click on different tabs in the same workbook.

Let me know if you have any more problems.

Kelly

Automatic Time Stamp in Excel

So the following is a question I had from one of my students:

Kelly, can you help me modify the formula on the “Delivery Time” column? Instead of having the date appear, I’m trying to have the time that I enter it in the spreadsheet?

Thanks

Tom
Hi Tom,

Sorry about the delay in getting back to you! Did you mean like a time stamp? When did you want that to happen? For example, when you enter X date, the time stamp would show up.

Just clarify and I'll look into it for you.

Thanks, Kelly

Yes something like that. I want it to show up with a time instead of a date like the formula is right now.

Hi Tom,

I THINK I have it!

Before you open the spreadsheet, you need to set up Excel to run properly for it. You have to turn on the iterations.

File > Options > Formulas

I'm including the article that goes with how to do this. But don't worry to much with understanding it. I think the formula is working the way you want it.

Thanks!Kelly

Here’s the bottom line:

Tom wanted the current time to appear automatically in the Delivery Time column when he entered a date in the Delivery Date column. Basically a time stamp.

I found the answer to this on one of my favorite websites, Chandoo.org. It’s a fabulous Excel website!

This is the link to the article I used, AutomaticallyInsert Timestamps In Excel Sheet Using Formulas.

First you have to set up Excel to enable circular cell references.

“Now, by default iterations in formula computation is turned off. That means when you write a circular formula or create a circular reference, excel shows you a message warning you and it doesn’t evaluate the formula. To turn the iterative calculation, and thus enable usage of circular formulas, go to menu > tools > options > calculation tab and check the iteration box. In excel 2007, you can go to office button > excel options > formulas > iteration area.”

In Excel 2013-2016 it’s File > Options > Formulas> in the Calculation Options section.

 

Now in the Delivery Time column, you would put the following formula:

=IF(Q2<>"",IF(R2="",NOW(),R2),"")

“What it does?

It checks cell C3 (Q2) and whenever it is not empty it runs the circular formula IF(B3 (R2)="",NOW(),B3 (R2)) which fetches NOW() value only if the cell B3 (R2) doesn’t already have a value, thus serving timestamps.

The above formula works only if you have enabled iterative calculation mode as described above.”

Even if you don’t completely understand the concept, trust that it works!

Later I’ll post how we calculated the Transit Time and On Time columns.

Downloads

Time Stamp with circular formulas.doc

TimeStamp sheet.xlsx

An Introduction!

Let's answer the obvious first question...what the heck does PCF1 mean?? Answer to follow in the next post!

I'm starting this blog in an attempt to help people with their Microsoft issues. California Computer Schools offers "tech support" to all of our students. This means after attending a class with us, students can contact us with any questions they might have with regards to the class. Now I bend the rules on a regular basis and pretty much try to answer any questions that come my way. I've decided to share some of those questions with anyone who might be interested or maybe they have had the same issues in the past.
My plan is to update this blog weekly using questions I have received in the past. Maintaining my student/clients privacy is a priority to me, so I will remove any personal information associated with the question. Not only will I provide the question and answer, but I will also provide the research I did to resolve the problem. Going forward with new questions, I will make sure to site the sources that I used, however I may not be able to site all the sources for some of my older questions. My apologies in advance to any resource I failed to site. Please contact me and I will update my post. 
Please feel free to ask any questions you might have and I'll give it my best effort to assist you!