June Cartwright

Acadia, Inc.
3079 Indiana Ave.
Omaha, NE 58712

November 5, 2021

Stephen Nielsen
Tech Leaders
P.O. Box 8940
Williamsburg, VA 10089

Dear Mr. Nielsen,

Thanks for the phone call yesterday. Our company is excited to work with your
group to create this exciting new software publication.

I have enclosed a content draft for your perusal. After our conversation
yesterday, our writers came up with brief articles for all five Microsoft Office
applications that we discussed: Access, Excel, Outlook, PowerPoint and Word.
We tried to keep the tone of the writing light, with simple language that is easy to
read and understand.

We are still working on the design and layout of these bulletins, but agree that
your concept of one page with content on the front and back is very good. This
should serve as nice framework for the tone and content of each bulletin.

Please take your time when reviewing the following articles, and don’t hesitate to
contact us with questions and comments.

Sincerely,

June Cartwright
Access
Filtering out the junk

Use filters to view the data you need

Access objects, such as queries and reports, make it easy to control and restrict the data you
want to view in a database. But sometimes you need to narrow the focus of data to such a
degree that creating a whole new query or report doesn’t make sense.

For example, it doesn’t make sense to create a new query to view products
within a certain price range. If you did that, you’d quickly accumulate query upon query in your
database: either that, or you’d constantly be changing a specific query to view different results.
Neither solution is efficient, or practical, resulting in a bloated, oversized file and a big change in
the overall design of your database.

The better way to narrow the data is to use filters. Filters are commands that
display records that match criteria that you set. For example, if you wanted to view sales over
$100,000, rather than scanning an entire Quarterly Sales query, you could apply a filter with the
criteria, or expression, >100,000. Filtering is a fast and easy way to limit the number of records
that are displayed.

In this article, we’ll show you some common ways to filter data from just about
anywhere in a database.

About filters

With all the different ways to view data, how do you know when and if you want to use a filter?
Here are a few situations where a filter is probably your best tool:

· You want to temporarily narrow your data in a form or table when working in Datasheet
view or Form view.

· The criteria for the data you want to view changes frequently.

Filters are very flexible. You can apply multiple filters at a time to view specific
data, and you can also turn them off at any time. You can even save filters to use them again in
the future.

Filter by Selection

One of the easiest ways to apply a filter is to find the data you want to filter for, and then filter by
it. Look at the example below. To view only customers from Texas, find the data you want to
filter by, right-click that data—TX—and select Filter By Selection. The table is filtered so that
only records from Texas appear. You can also select the data and click the Filter By Selection
button on the toolbar.

Filter by Excluding
This command is the opposite of filtering by selection. With this command, the field you want to
hide is filtered out. So, using the example above, if you right-clicked a record with TX in the
State field and selected “Filter Excluding Selection” from the shortcut menu, all the records in
the database would appear except records with TX in the State field.

Filter by Input

If you want to use a more advanced filter, you can enter the criteria you want to filter for, and
even filter by several criteria at a time. To filter by input, write an expression to be used as the
criteria by which data is filtered. Here are three ways to enter filter criteria:

Filter For: The quickest way to filter by criteria is through the shortcut menu in Access. Right-
click the database, enter the expression you want to use as criteria in the Filter For text box and
press <Enter>.

Filtering By Form: When you filter by form, the expression you use as criteria is entered in a
data field. Click the Filter by Form button. A new view appears where you can enter criteria for
each data field. When the criteria you want to filter for is set, click the Apply Filter button on the
toolbar.

Advanced Filter/Sort: The criteria are entered in the Advanced Filter/Sort window, similar to
setting query criteria. Select Records > Filter > Advanced Filter/Sort from the menu to open the
window. Double-click the field(s) you want to filter by in the window to add the field to the criteria
list.

Remove Filter/Sort

If you don’t want a filter to be applied any longer, or would like to apply a new filter, you can turn
it off or remove it. Right-click the database, then select Remove Filter/Sort from the shortcut
menu. Or, click the Remove Filter button on the toolbar.

Excel
2…4…6…8 everyone consolidate

Combining data ranges from multiple worksheets

These days, you can consolidate your credit card debt, your student loans, or your company’s
subsidiaries. But did you know that you can also consolidate data from multiple Excel
worksheets? For example, if you have sales data from three different offices on three different
worksheets, Excel can total them for you on another worksheet.

Three ways to consolidate

Excel provides three ways to consolidate information: by position, by category, or by using 3-D
references.

· Consolidating by position is the most restrictive. To use this approach, data in all the
worksheets must be arranged in exactly the same order and location.

· Consolidating by category is slightly less restrictive—all the worksheets need to have the
same row and column labels in order to be consolidated, but the data can be arranged
differently. Excel uses the labels to match the data.

· Consolidating using 3-D references is the least restrictive method. You can create formulas
to consolidate data that is arranged in any fashion, and the consolidation updates
automatically when the source data changes.

Using either of the first two methods, you can consolidate manually or set it up to update
automatically when the source data changes, but you can’t change which cell ranges are
included in the consolidation.

Now let’s look at each of the methods in more detail.

Consolidating by position

When to use: If data is laid out in identical cells in all consolidating worksheets.

Before you consolidate by position, make sure that the data is arranged in
labeled rows and columns without blank rows or columns. Each of the ranges you want to
consolidate needs to be on a separate worksheet, with a blank worksheet used for the
consolidation’s destination. Each range needs to be laid out the same because you’re going to
be combining data from the same cell in each of the ranges.

Next, click the upper-left cell of the area where you want to put the consolidated
data. Select Data > Consolidate from the menu. The Consolidate dialog box appears. Click the
Function list arrow and select the function you want to use to consolidate the data: for example
—Sum, Count or Average.

Next, click the Reference button, click the first worksheet tab you want to
consolidate, and select the range you want to consolidate. The range shouldn’t include the row
or column labels. Press the <Enter> key, then click the Add button.

Click the Reference button again and repeat the process to select the ranges on
the other worksheets. If you want the consolidation to update automatically whenever the
source data changes, click the “Create links to source data” check box to select it. When you’re
ready to consolidate, click OK.

The values from the selected ranges are combined on the consolidation
worksheet using the function you selected. You’ll need to copy row and column labels from one
of the worksheets onto the consolidation worksheet.

Consolidating by category

When to use: If consolidating cell ranges have different layouts, but matching labels.
 This process is somewhat similar to consolidating by position, except that Excel
will look at the row and column labels to match up the cells that should be consolidated—so you
don’t need to worry if the rows and columns are in different orders.

Before consolidating, make sure that label spelling and capitalization is identical
on each of the worksheets you want to consolidate. Then, click the first cell where you want the
consolidation to appear and select Data > Consolidate from the menu to display the Consolidate
dialog box. Select a function, then select the ranges like you did when you consolidated by
position—although you’ll want to also select the labels in the range this time.

Next, click both the “Top row” and “Left column” options to tell Excel where the
labels are located in the source ranges; the labels will be copied onto the consolidation
worksheet. Any labels that don’t appear in all of the source ranges will appear in separate rows
or columns on the consolidation worksheet, along with their corresponding data cells. Click the
“Create links to source data” check box, if desired, then click OK. The data is consolidated and
appears along with its labels on the consolidation worksheet.

Consolidating by 3-D references

When to use: Any time.

Consolidating with 3-D references is the most versatile and powerful way to
consolidate data in Excel because there is no prescribed format for the consolidating data, and
the consolidation is updated automatically when source data is changed. To consolidate using
this method, copy the labels you want to use from the source worksheets to the consolidation
worksheet, then click the first cell where you want to put the consolidated data.

Type a formula that references the source cells in each worksheet that includes
the data you want to consolidate. For example, you could combine three different cells on three
different worksheets by typing =SUM (Sheet2!A6,Sheet3!B7,Sheet4!D2). Tip: Instead of typing
each cell reference, you can click the cells you want to include.

As you can see from the example below, the cells don’t need to be in the same
position on each sheet, or even have the same labels, to be consolidated using this method.
And, because the consolidation uses cell references, they will automatically update when the
source cell ranges are changed.

PowerPoint
And the winner is…

Create award certificates in PowerPoint

A rule is a very powerful tool that helps you manage your e-mail messages by having Outlook
automatically perform specific actions. When you create a rule, Outlook applies the rule to any
incoming or outgoing messages.

To give you an idea of what you can do with rules, take a look at the following
list. You can set up a rule to automatically perform any of the following actions (and more):

· Forward all messages from Jim Brown to your manager, as soon as they arrive in your Inbox.

· Move all incoming messages that have the word “Picnic” in the Subject box to a Picnic folder.

· Color-code all messages with a high importance level.

There are two simple ways to create a rule: by example, and using the Rules
Wizard. Creating a rule by example is a new feature in Outlook 2003, and is probably the
easiest way to create a rule. In this article, you’ll learn how to create a rule using both methods.

Creating a rule by example

First you need to find the message that you want to use to create the rule. For example, if you
want to automatically move messages from your boss into a folder named “Work,” find and open
a message from your boss.

Click the Create Rule button on the Standard toolbar to open the Create Rule
dialog box.

First you need to specify the conditions for the rule. Since you want to move all
messages from your boss into a specific folder, click the “From…” check box to select it.

Now you need to tell Outlook what you want it to do whenever a message from
the selected individual arrives in your Inbox. Since you want all messages from your boss
moved to the Work folder, click the “Move e-mail to folder” check box to select it. Select the
appropriate folder from the Rules and Alerts dialog box (click Select Folder to open this dialog
box if it doesn’t appear automatically) and click OK.

Click OK to save the rule and close the Create Rule dialog box. Now, anytime a
message arrives from your boss, it will automatically be sent to the Work folder.

Creating a rule using the Rules Wizard

Select Tools > Rules and Alerts from the menu to display the Rules and Alerts dialog box. This
is where you can add new rules and manage existing ones.

Click the New Rule button to start the Rules Wizard. You have two options for
creating a rule in the Rules Wizard: creating a rule from a template, or starting with a blank rule.
The first option lets you create a rule using common rule templates, while the second option lets
you create a rule from scratch. The second option is a little more complicated than the first, but
it is much more flexible.

Select the “Start from a blank rule” option and specify whether or not you want
the rule to check incoming or outgoing messages. Click Next when you’re finished.
 Now you need to tell the Rules Wizard what conditions to look for. For example,
you might tell the Rules Wizard to look for any messages that have the word “Invoice” in them.

To search for messages that only contain certain words, click the “with specific
words in the subject” and “with specific words in the body” check boxes. Then, in the Edit
window, click the first “specific words” link, type the word you want to search for, click Add, and
click OK. Do the same for the second link, and then click Next.

The next page of the Rules Wizard appears. Here you need to tell Outlook what
action it should take whenever it encounters a message that meets the conditions you specified.

To have a message automatically forwarded to someone else, click the “forward

it to people or distribution list” check box. Then, in the Edit window, click the “people or
distribution list” link, select the person you want to forward the message to, and click OK. Click
Next.

Specify any exceptions to the rule, and click Next. Then, give the rule a name
and click Finish. Click OK to close the Rules and Alerts dialog box.

Managing rules

Most likely you’ll want to modify or delete a rule at some point. To do this, use the controls in the
Rules and Alerts dialog box. To display the Rules and Alerts dialog box, select Tools > Rules
and Alerts from the menu.

Outlook
Make your own rules

Automatically manage your incoming and outgoing messages by creating rules

Everyone likes to hear that they’ve done a good job. So why not tell ‘em in style?

This article will show you how to create award certificates in Microsoft
PowerPoint. A certificate created in Microsoft PowerPoint is a great way to tell an employee that
their hard work has caught your attention, or to show your students that they are #1! No matter
what your intentions, creating a certificate in Microsoft PowerPoint is fun and easy.

Let’s get started

There are two ways to create an award certificate in Microsoft PowerPoint: using a pre-designed
template, or creating your own certificate design from scratch. Creating a design from scratch
gives you the most freedom, but if you’re not the most creative person in the world, using a
template is probably a better option.
Finding templates online

A great place to find templates online is Microsoft Office Online. They’re free, easy to download,
and rather tasteful.

To find a template online, visit office.microsoft.com and click the Templates tab
on the left-hand side of the Web page. In the Search bar near the top of the page, make sure
“Templates” appears in the list box and type “certificates” in the text box. Click the Go button to
start your search.

After a few moments, a list of templates appears. To preview a template, click on

it. Check the requirements for the template (for example, “PowerPoint 97 or later”) and make
sure you meet them. Then, if all looks good, click the Download Now button to start the
download process.

Once the download process is complete, the template appears in the PowerPoint
program window. Now you’re free to customize the template to meet your specific needs.

Modifying text

Once you’ve downloaded an appropriate template, it’s time to customize it to meet your needs.

The first thing you’ll probably want to do is replace the sample text with your own.
You don’t want the placeholder text—such as [Organization Name] and [Presenter’s Name
Here]—to appear in the printed document!

Changing text is easy—simply select the text you want to replace and type the
new text. If the new text doesn’t fit, resize the text box. (Hint: To resize a text box, click and drag
one of its sizing handles.)

Working with graphic elements

In addition to modifying text, you can also adjust the template’s design. Don’t like the yellow
background of a template? Want to insert your company’s logo? You can do all of this and more
using PowerPoint’s formatting commands.

To add a creative touch, try inserting your company’s logo as the certificate’s
background. To do this, select Insert > Picture > From File and insert your company logo onto
the slide. Right-click the logo, select Format Picture from the menu, and click the Picture tab, if
necessary. Under Image Control, click the Color list arrow and select Washout from the list.
Click OK. Right-click the logo once again, and select Save As Picture from the shortcut menu.
Save the picture.

Next, select Format > Background from the menu, select Fill Effects from the list,
and click the Picture tab. Click Select Picture, navigate to the washed-out logo image you just
saved, and click Insert. Click OK, and then click Apply to apply the logo to the certificate as a
background. Not all pictures work well as backgrounds, so try another image if the first one
doesn’t work out.

If the background doesn’t appear at all, an object might be covering it on the

slide. If this is the case, either resize the obtrusive object, or delete the object altogether.
Word
Indent with confidence

De-mystifying indents with tab stops

It’s possible that you’re already a pro when it comes to left tab stops and first line indent
markers. But if you’re like many Word users, you’ve probably pounded out hundreds of memos,
reports, and letters in Word, while remaining a little perplexed by the strange cast of characters
perched on the horizontal ruler at the top of the document.

In this lesson, we’ll unravel the mystery of paragraph formatting items such as
the hanging indent and left tab stop so that you can use them purposefully, not just accidentally.

In the margins

The first thing to understand about document formatting is margins. Margins are the blank
spaces around the edges of the page. The margins appear on the rulers as gray areas. You can
adjust margin size by selecting File > Page Setup and making adjustments on the Margins tab,
but the default margins are usually fine for most documents.

Inside of the margins, you can adjust the horizontal layout of your text and other
objects using alignment, indents, and tab stops. We’ll look at each of these.

Text alignment

Alignment is pretty straightforward. By default, text is aligned to the left side of the page, but you
can click the Center, Align Right, and Justify buttons on the Formatting toolbar to change the
alignment. Justified alignment adjusts the spacing between words so that the text is flush with
both the left and right margins, creating a clean look, like that seen in newspaper columns.

First line indent

The next tool at your disposal is indents, which include the first line indent, hanging indent, and
left indent. By default, there are no indents in Word documents, so the three markers appear
near the left end of the horizontal ruler, up against the left margin.

The first line indent allows you to indent only the first line of a paragraph—a
common document format. You can choose the size of the indent by clicking the First Line
Indent marker on the ruler and dragging it to the right. Or, you can set a first line indent by
selecting Format > Paragraph from the menu, selecting First line from the Special list, and
adjusting the size of the indent using the By arrows. By using this dialog box, you can choose a
very exact indent distance.
 Note: Indent formatting is applied to selected paragraphs and following
paragraphs will inherit the formatting.

Hanging indent

A hanging indent allows you to indent the paragraph text after the first line. These are often
used for bulleted or numbered lists.

You can create a hanging indent by clicking the Hanging Indent marker on the
horizontal ruler and dragging it to the right. Notice that the Left Indent marker comes with it, but
the First Line Indent marker stays put. This way, all of the text except for the first line becomes
indented.

Much like the first line indent, the hanging indent can be set more exactly by
selecting Format > Paragraph from the menu, selecting Hanging Indent from the Special list,
and adjusting the size of the indent using the By arrows.

Left indent

When you click and drag the Left Indent marker on the horizontal ruler, the entire paragraph is
indented. If there is already a first line or hanging indent, those indents are preserved, but the
entire paragraph, including those indents, shifts over.

Another easy way to move the left indent is with the Increase and Decrease
Indent buttons on the Formatting toolbar. Each click of these buttons adjusts the indent by
another half an inch. Or, for a more fine-tuned approach, you can select Format > Paragraph
from the menu and click the Left arrows in the Indentation section until you reach the desired
indent.

Right indent

The right indent adjusts the right edge of your text. Simply slide it on the horizontal ruler to
define the right edge of a paragraph. Or, select Format > Paragraph from the menu and click
the Right arrows in the Indentation section.

That’s the extent of indents, which brings us to the question: How are indents
different from tab stops?

Tab stops 101

Tab stops are little black markers that you can stick on the horizontal ruler to control the <Tab>
key. By default, when you press <Tab> the cursor moves half an inch to the right. By using tab
stops, you can change the way the <Tab> key works.

Unlike indents, tabs come into play only if you press the <Tab> key—in which
case your cursor moves to the point on the ruler where the tab stop is set. You can set tab stops
with left, right, center, or decimal alignment. This means that when you start typing at the tab
stop, the text will go in the specified direction. Or, in the case of a decimal stop, which is used
for numbers, the characters will align with the decimal point located directly at the tab stop. This
is useful for lining up a column of numbers by the decimal point.
 Note: Word 2000, 2002 and 2003 also offer a Bar tab that inserts a short vertical
line where you put the tab stop.

To add a tab stop, click the Left Tab button at the left end of the horizontal ruler
until it changes to the type of tab you want to use. Then, click the ruler where you want to insert
the tab stop. The tab stop appears on the ruler.

Now when you press <Tab>, the cursor moves to the next stop. As you type, the
text takes on the alignment you selected. You can add multiple tab stops, and you can also
remove a tab stop by dragging it off the ruler.