Navigating Excel

The cells found in Excel, or any spreadsheet program, are very similar to a table in Word. You can add text or formulas to these cells, and you can manipulate their appearance as needed.

In order to effectively use a spreadsheet, you must be able to access the information and navigate through the cells.

Here are a few basics of spreadsheet cell and page navigation;

Basic Cell Navigation

Action	Key Stroke option 1	or	Key Stroke option 2
Right One Cell	Tab	or	Right Arrow
Left One Cell	Shift + Tab	or	Left Arrow
Down One Cell	Enter	or	Down Arrow
Up One Cell	Shift + Enter	or	Up Arrow

 Basic Page Navigation

Action	Key Stroke
Beginning of the Worksheet	Ctrl + Home (I use this a lot)
End of the Worksheet	Ctrl + End (I use this a lot)
Beginning of the Row	Home or Ctrl + Left Arrow
End of the Row	Ctrl + Right Arrow
Beginning of the Column	Ctrl + Up Arrow
End of the Column	Ctrl + Down Arrow

In addition to using key strokes to navigate through Excel, you can also use your mouse to move to or select individual cells and groups of cells.

Single clicking on a cell, selects the cell, creating a thick black boarder around the cell it’s self.

Double clicking a cell, causes the cell to go into ‘Edit Mode’, inserting an I beam in the cell and allowing for typing directly in the cell.

Another way to navigate to a cell is by using the ‘Name Box’. This is located directly above the Column ‘A’ Heading, and it tells you the name of the cell that you currently have selected.

Selecting multiple cells

Action	Mouse	Mouse & Key Stroke	Name Box
Adjacent Cells	Click in the 1st Cell and Drag to the last Cell	Select 1st Cell, Hold CTRL Key, Select last Cell	Type Range in the Name Box separated by a Semi colon
Non Adjacent Cells	N/A	Select 1st Cell, Hold SHIFT Key, Select next Cell	Type Range, or specific cell, in the name box separated by a Comma
Entire Row	Click on the Row Heading (i.e. 1, 2, 3, etc…)	Click in the cell at the beginning of the Row Hold CTRL + SHIFT + Right Arrow	N/A
Entire Column	Click on the Column Heading (i.e. A, B, C, etc…)	Click in the cell at the beginning of the Column Hold CTRL + SHIFT + Down Arrow	N/A

Also keep in mind that each Workbook can have multiple Worksheets. These sheets are shown as ‘Tabs’ at the bottom of your Worksheet. These ‘Tabs’ are only visible if your Workbook is maximized (small Center button in the top right of your screen, to the left of the close button).

Hopefully this information was beneficial to you. If you need more guidance with Excel Spreadsheets, please contact Office Solutions ^ME!

Excel Formula Basics

Microsoft Excel is a very powerful program. It can be even more powerful when the formulas are used, and used correctly. In most cases, Excel formulas are not really that difficult… as long as you remember your 8^th grade math class.

Here are the basics;

Process	Arithmetic Symbol	Function	Formula
Addition	+	SUM	=__+__	or	=SUM(__,__)
Subtraction	-	IMSUB	=__-__	or	=IMSUB(__,__)
Multiplication	*	PRODUCT	=__*__	or	=PRODUCT(__,__)
Division	/	QUOTIENT	=__/__	or	=QUOTIENT(__,__)

 

The ‘Formulas’ above are typed directly into the cell that you wish to show your answer, of course replacing the ‘__’ with the information you wish to calculate. In addition, you can replace the ‘__’ with the actual cell reference that you wish to calculate.

Here’s an example of adding two values using cell references;

	A	B	C
1	100	50	=A1+B1
2	50	100	=SUM(A2,B2)

 

Column ‘C’ shows the formula that you would enter to get the answer of ‘150’. The benefit of using formulas is that if you change cell A2 to ‘150’, then your formula will automatically update the answer to ‘250’.

A few more Functions that can be helpful include;

• Count – simply counts the number of occurrences in a range of cells
• CountIf – counts the number of occurrences of a specific value in a range of cells
• Max – returns the maximum value in a range of cells
• Min – returns the minimum value in a range of cells
• Average – returns the average value in a range of cells

These (and much more) are all found on the ‘Formulas’ Tab à ‘More Functions’ button à ‘Statistical’ menu

The help feature (located in the top right, below the close button) and the advanced screen tips (appear if you hover over an item in the toolbar) are both very useful when searching for the appropriate formulas for creating and optimizing your spreadsheets.

Bonus Tip:
When you select a cell, the formula will appear in the Address Bar.
To view all of the formulas in a spreadsheet, instead of the results, press Ctrl+` (press it again to revert back to the results view)

For more helpful tips and help with Excel Formulas, please contact Office Solutions ^ME!

Mail Merge 101 – Easy!

A Mail Merge is a way to create a document once and then easily recreate the document with alternate information in specified locations. For example, the most commons use of a Mail Marge is a letter that is being sent to multiple recipients. The contents or body of the letter is the same for everyone; however the recipient information needs to be customized for each letter. 

A mail merge allows you to create a letter for 500 recipients as easily (or easier) than you could create one for 5 recipients.

1. Create the letter that you intend to use in Microsoft Word
2. Create a spreadsheet that contains all the information that needs to be substituted.
   For example; First Name, Last Name, Company, Address, etc…It may look something like this -
   
   

   Suffix	FName	LName	Company	Street	City	State	Zip
   Ms.	Jane	Smith	ABC Corp	123 C St	Portland	ME	04101
   Mr.	John	Smith	123 Inc	456 N St	Portland	ME	04101

   Hints: All column headings should be a single word, The ‘Zip’ column may need to be specially formatted if the zip codes you are using start with ‘0’. This can be done under the cell formatting options, Remember the name and location of the spreadsheet when you save it

3. With your letter open and your spreadsheet closed, navigate to the ‘Mailings’ tab (5^th from the left) at the top of Microsoft Word toolbar (or Ribbon).
   Choose the ‘Select Recipients’ option (4^th from the left)
   Choose ‘Use Existing List…’ (Second option). This will open another window
4. Navigate to the location where you stored your spreadsheet
   Select your saved spreadsheet, Click ‘Open’
   
5. This will give you a list of the sheets available in that file, generally yours will be ‘Sheet 1’ unless you named it otherwise
   
6. You now have some additional options available to you on the ‘Mailing’ tab on the top of the page
   One of the new options is ‘Insert Merge Field’ this should give you a list of all of the column headings in your spreadsheet.
   
7. Now you can insert the desired fields from your spreadsheet into your letter template by clicking on them

   Here is an example;

   Hi «Fname»,
   I hope business is going well at «Company»!

   Hint: Any blank fields in your spreadsheet will be skipped in your letter

8. Once you are done adding your merge fields, you can ‘preview results’ or ‘Finish & Merge’
   Both options are also found on the ‘Mailings’ tab toward the right side of the menu

Hopefully this quick Mail Merge 101 tutorial helps you become more efficient and effective!

For more help with your mailings or any other word processing and administrative functions, contact Office Solutions ^ME!

3 Excel (2007) Formatting Tricks

Excel is a great business tool! Although, it has many features the common user is unaware of, but could greatly benefit from!

Here are a few tips on formatting that I use regularly;

1. Table Formatting – Formatting your information as a table gives you added features such as; predesigned templates, filters, and easily adding rows or columns. 
   • To do this: Select the information in your spreadsheet. Navigate to the Home Ribbon, where you will find the ‘Format as Table’ button in the Styles Group. Choose your preferred style.
2. Print on 1 Page – Adjusting your printing options to 1 page width is easily done and can make a huge difference in readability!
   • To do this: Navigate to the Page Layout Ribbon, where you will find the ’Scale to Fit’ group. In that group, you can choose how to alter how your spreadsheet prints widthwise or lengthwise by choosing the number of pages you wish to span.
3. Hide Cells – Hiding confusing (or unnecessary) information can make your spreadsheets much more user-friendly! You can hide columns, rows, or even whole spreadsheets in a workbook.
   • To do this: Right click on the column heading, row heading, or page tab that you would like to hide. From the shortcut menu, choose ‘Hide’.
   • To unhide, simply right-click and choose ‘Unhide’.

 

For more tips and tricks with using Excel, please feel free to contact us at Office Solutions ^ME!

10 Tips for Maximizing the Usefulness of Your Spreadsheets!

Spreadsheets (such as Microsoft Excel) are very powerful tools and when used correctly, they can make your life a lot easier. Here are a few tips to help you utilize your spreadsheets more efficiently and effectively, as well as increase readablility;

1. Use Boarders Around Cells – spreadsheets are generally created to organize numbers and large amounts of information, and while you can see the lines on the computer screen as you are creating the spreadsheet, these lines will not print unless you add them as boarders.
2. Use Row and Column Titles – these are essential! Don’t assume that you don’t need to name your rows and columns, plus this can come in very useful when transferring information to charts or graphs and even in writing formulas later on.
3. Use Formatting – Make your rows and columns bold, highlight important information with different colors, bold boarders, or larger font.
4. Format Information Correctly – If you are working with money insert dollar symbols and put a double bottom boarder on total rows. If you are calculating percentages, be sure that the percent symbol is included and you have the correct number of decimal places needed.
5. Create Multiple Sheets – Instead of making multiple workbooks or trying to cram everything on one spreadsheet, utilize multiple sheets (tabs at the bottom) within one workbook for related information. Make sure each of the sheets within a workbook are named appropriately.
6. Insert an appropriate header/footer – Insert an appropriate header or footer on all of the pages of your workbook. This might include the company name or your name, the date, and/or the project name.
7. Don’t make the same changes twice – If you need to make the same changes to multiple sheets, you can change all of your sheets at once by right clicking on any worksheet tab and selecting all before making your changes.
8. Utilize Graphs and Charts – Many people respond better to information when presented visually. Charts and graphs are great ways to accomplish this. And when the charts or graphs are created within your workbook, they will update automatically as the information is changed.
9. Use Formulas for calculations – Formulas are one of the best parts about utilizing spreadsheets! Making them work for you is critical; they can save you a lot of time, effort, and mistakes if you can utilize them correctly. Any time you are manipulating information that is in another cell, chances are there is a formula that will do it for you.
10. Utilize the Help feature – Any time you do not know how to accomplish something (i.e. you are searching for a specific formula), try using the help feature. In most programs, this is accessed by clicking help or the F1 will usually enable a help feature.

Bonus: Save often – As with any document you are creating, make sure you save and save often. When creating spreadsheets, you can alter and input a lot of information in a short amount of time and losing it can be quite frustrating!

I hope this information was helpful,. Feel free to comment with thoughts or questions! I look forward to hearing from you!

Kathrine Farris
Owner/Administrative Professional
Office Solutions ^ME