Microsoft Office

/Microsoft Office

How to Create a Resource Calendar in MS Project

2019-09-30T11:58:15-04:00

The resource calendar in Project lets us take the resources' non-work  time into account. There are religious, working-two-jobs, and a few other reasons why this might come into play. So once the project calendar is established, we can create a variation of it as a resource calendar. We start by double-clicking the resource name in the Resource Sheet. We go to the Resource Information, and make sure we’re on the General tab of the dialog box. If the resource has a range, or ranges, of dates during which s/he will be available, we set them in the Resource Availability space at [...]

How to Create a Resource Calendar in MS Project2019-09-30T11:58:15-04:00

How to Use Select Case in Access VBA …and Why

2019-09-30T13:34:01-04:00

The Select Case statement in Visual Basic for Applications sometimes "competes" with the If...Then...Else statement, because the two can both be used for testing information. When we want to find out about more than one thing, condition, or what have you, we sometimes need to ask the equivalent of several questions. Which way we go, which type of code, is often our choice. All this emerges from the fact that computers ALWAYS need to do one thing at a time—they can’t make intuitive leaps, as humans can. The If...Then...Else asks "If" something is true, much as in the IF function in [...]

How to Use Select Case in Access VBA …and Why2019-09-30T13:34:01-04:00

How to Clean Up Reports in Microsoft Access

2019-09-30T11:49:47-04:00

There are a few important and helpful things to watch out for in Access reports. They spring from the fact that, unlike most other database components, reports often have to stand on their own. So their formatting and layout have to be more complete and user-friendly up front. One thing to keep an eye on is the layout of field labels. If the report includes relatively few fields, the labels can often go in the page header, one per column. But if the report has more than six or eight fields, labels might go better right above or next to each [...]

How to Clean Up Reports in Microsoft Access2019-09-30T11:49:47-04:00

Introduction to Excel PowerPivot Webinar

2019-09-13T15:13:59-04:00

In this free Excel PowerPivot webinar, Excel Instructor Seth Bonder explains how to add PowerPivot functionality into Excel, how to create a data model and much more. To learn more about PowerPivot, or to enroll in one of our PowerPivot classes, please visit our Excel PowerPivot Course page. https://youtu.be/KhOYyMWkDv8

Introduction to Excel PowerPivot Webinar2019-09-13T15:13:59-04:00

How to Build an Error Handler in Access VBA

2019-09-30T13:37:34-04:00

Dealing with errors in VBA code, especially in Access, is a vital part of setting up procedures. If nothing else, an error-handler can give the user a better idea of what’s glitching, if something does. And having a message box come up to give the user even moderately clear information can be a huge help. It also helps the database admin person, and the VBA coder. There are normally three main steps to setting up an error handling routine. First, right up at the top of the procedure, we need a line to tell it what to do for errors. Some [...]

How to Build an Error Handler in Access VBA2019-09-30T13:37:34-04:00

How to Add a Calculated Field to an MS Access Query

2019-09-30T11:44:46-04:00

Calculated fields are a really big help in Access; they let us derive data from existing information. And they’re flexible, too. If one of the source items changes, the calculation updates immediately, just like an Excel formula. But some people aren’t aware you can add them to a query. And this is an especially cool thing, because every time we run a query, we get the latest info. One example where we might use this capability would be calculating a restock—how many items we need to add. We can open the database, check the table we draw data from, and design [...]

How to Add a Calculated Field to an MS Access Query2019-09-30T11:44:46-04:00

How to Use the Organizer in Microsoft Project

2019-09-30T13:46:52-04:00

The Organizer is one of the lesser-known features in Project. And it’s a shame that this one is overlooked. It can be a major help in both building templates and making already-built elements easily available. One common example is calendars. If you build a company calendar, and want it in other projects, you can copy it anywhere with the Organizer. Since calendars can take some work to create, they are among the most frequently-copied items in the program. First, we open the source and destination projects. Any two can be used. We then go to the View tab, and either Task [...]

How to Use the Organizer in Microsoft Project2019-09-30T13:46:52-04:00

How to Create a Template in Microsoft Project

2019-09-30T14:04:58-04:00

A template in Project, as in most other programs, is a blank form. We fill in the spaces, as on a tax form or license application. The more complex the job, the more complex the form. So when we’re managing a project, any help we can get doing a bunch of similar projects is really good. The key is having a project file that’s mostly or entirely complete—i.e., the project is done. Because if we know the project ran successfully, we can frequently use it to guide others of the same sort. So we open the file in question, and see [...]

How to Create a Template in Microsoft Project2019-09-30T14:04:58-04:00

Using the Split Form and Datasheet Form in Microsoft Access

2019-09-30T14:06:33-04:00

The idea of the split form in Access is rather like its cousin in Project. We set up a view with the “big picture” or large-scale view in one portion of the window (usually the top), and details in the bottom. Sometimes vice versa, but the thinking is to have both available for convenience. (I'll use the datasheet form shortly, as another example.) Creating a split form is very easy, as it’s listed within the regular forms. We select the table we want to work with, go to the Create tab->Forms->More Forms, and click Split Form. The result is to show [...]

Using the Split Form and Datasheet Form in Microsoft Access2019-09-30T14:06:33-04:00

How to Use Summary Tasks in Microsoft Project

2019-09-30T14:26:02-04:00

When we create a project plan, summary tasks are like the main points in a term paper outline. They mark off the highest-level things we need to do in the project. They’re the main stages or phases we look at. So they’re a useful tool for visually organizing the project, in a user-friendly way. The good news is, we don’t have to insert them immediately. We can start by just writing down what we need to do, and clean up later. Here we have a list of tasks for doing some house painting. (Yes, I’ve done this for real. Yes, that’s [...]

How to Use Summary Tasks in Microsoft Project2019-09-30T14:26:02-04:00

How to Create Calculated Fields in Microsoft Project

2019-09-30T14:26:59-04:00

Project allows for things like calculated fields and other custom data. It holds some fields “in reserve” aside from the ones set up for task names, durations, etc. This way, users can insert data the program couldn’t know about in advance. (I mentioned custom text fields a while back, but a calculated field is a slightly different thing.) Having some experience with formulas, in the style of Access or Excel, will help. And making sure one has a clear idea of what needs to be calculated is important, naturally. We start by calling up the Custom Fields box. It’s under the [...]

How to Create Calculated Fields in Microsoft Project2019-09-30T14:26:59-04:00

How to Create a Parameter Query in Access

2019-09-30T14:43:04-04:00

The parameter query, while easy to set up, actually depends on a sort of quirk in the program, as we’ll see. But regardless, it’s a very powerful feature; it allows many variations on one question in a database. Ranges of dates, differences in spelling, and many others can fit a parameter query. A parameter query on a range of dates uses the “Between—And” keywords, and has the user enter a starting and ending date as indicated. (Any criteria of this sort go in the Criteria row in Query Design view.) The quirk is, when we want the program to ask the [...]

How to Create a Parameter Query in Access2019-09-30T14:43:04-04:00

Creating an Action Query in Microsoft Access

2019-09-30T14:44:11-04:00

The action query in Access is a different thing from the normal query we create, called a “select” query. The latter simply selects data and shows the result. But an action query edits data, or even deletes it. And the key thing one needs to know about this is one cannot use Undo to reverse the effect. So though we can easily create action queries, we need to treat them carefully. Luckily, there is a way to test them first. We can use a food database for our example. Let’s say we need to increase prices on soft cheese (Category 2) [...]

Creating an Action Query in Microsoft Access2019-09-30T14:44:11-04:00

How to Create a Subquery in Microsoft Access

2019-09-30T14:45:29-04:00

Just as there can be subforms and subreports within an Access form or report, there can be a subquery within a query. As the term implies, a “query within a query” allows the main query to be more specific, or complex. It sometimes requires a little bit of Structured Query Language, or SQL. But this is not a problem. Access is a “shell” over SQL as Windows was a shell over DOS, and the two get along fine. A typical example would be setting up a subquery based on text information, to allow more flexibility with the use of number fields [...]

How to Create a Subquery in Microsoft Access2019-09-30T14:45:29-04:00

Using the Section Break in Microsoft Word

2019-09-30T15:53:56-04:00

When putting together a complex document in Word, a section break can help us put together documents such as a book or manual, where one sometimes has to create multiple headers and footers, or allow for single columns, then multiple, then single again. A section break is like a fence between two farms—it tells the user “there’s a border and a change here”. Inserting a section break is very easy. Click at the end of whichever piece of text is the “previous” layout, arrangement, or whatever, then go to the Page Layout tab, Page Setup group, and click Breaks. There, we [...]

Using the Section Break in Microsoft Word2019-09-30T15:53:56-04:00

How to Create a Theme in Microsoft PowerPoint

2019-09-30T15:57:17-04:00

In PowerPoint, themes are an important part of the presentation. Even if nobody directly notices them, they are as necessary to the slide deck as the foundation for a house. Consistent appearance throughout the show improves understandability, and its professional look. Creating a theme is fairly easy; simply build the pieces and put them together. The first part is to set up  the fonts and colors needed. On the Design tab, looking to the right side of the Themes group, are dropdowns for Colors, Fonts, and Effects. We can’t edit the Effects, but the first two relate to many distinct business [...]

How to Create a Theme in Microsoft PowerPoint2019-09-30T15:57:17-04:00

Microsoft Access Keyboard Shortcuts

2018-12-24T12:17:15-05:00

SkillForge SkillSheet Microsoft Access 2010/13/16 Keyboard Shortcuts Click here to download the PDF version. Frequently Used Shortcuts   In order to… Press Select the active tab of the ribbon and activate KeyTips Alt or F10  (to move to a different tab, use KeyTips or the arrow keys) Open the Home tab Alt+H Open the Tell me box on the ribbon Alt+Q, then enter the search term Display the shortcut menu for the selected item Shift+F10 Move the focus to a different pane of the window F6 Open an existing database Ctrl+O or Ctrl+F12 Show or hide the Navigation Pane F11 Show [...]

Microsoft Access Keyboard Shortcuts2018-12-24T12:17:15-05:00

Microsoft Excel Keyboard Shortcuts

2018-12-24T12:01:27-05:00

SkillForge SkillSheet Microsoft Excel 2010/13/16 Keyboard Shortcuts Click here to download the PDF version.   Frequently Used Shortcuts In order to… Press Close a spreadsheet Ctrl+W Open a spreadsheet Ctrl+O Save a spreadsheet Ctrl+S Copy Ctrl+C Paste Ctrl+V Undo Ctrl+Z Remove cell contents Delete key Choose a fill color Alt+H, H Cut Ctrl+X Bold Ctrl+B Center align cell contents Alt+H, A, then C Format a cell from context menu Shift+F10 or Context key Add borders Alt+H, B Delete column Alt+H, D, then C   The Function Keys—What They Do Key Description F1 Displays the Excel Help task pane. Ctrl+F1 displays or hides [...]

Microsoft Excel Keyboard Shortcuts2018-12-24T12:01:27-05:00

How to Edit Contacts and the Business Card in Outlook

2019-09-30T16:19:11-04:00

Outlook 2016 has implemented a slightly different scheme for contacts and the business card in them, but the differences are mainly cosmetic. The basics are similar enough that users of the earlier versions won’t be at a loss. Once the program is open, the first thing is to switch to the Contact List (now also known as the People list). One can double-click where indicated to create a contact, and fill in the blanks, or edit an existing one. The thing some people have a little bit of a time getting just right is the business card part. Right-clicking and selecting [...]

How to Edit Contacts and the Business Card in Outlook2019-09-30T16:19:11-04:00

How to Format the Gantt Chart in Project

2019-09-30T16:21:08-04:00

Recently, I had a question about formatting the Gantt chart from a student who mentioned the issue of color-blindness. Since various kinds of this problem exist, knowing how to get around it can be helpful, just in case. As critical tasks are highlighted in red, it could be an issue. The Gantt chart, after all, depends at least in part on color, or so we normally think. But there are ways around it. To start the process, we go to the Gantt Chart Tools Format tab, and click the Format button in the Bar Styles group. We then have to go [...]

How to Format the Gantt Chart in Project2019-09-30T16:21:08-04:00

How to Set Up Custom Signatures in Microsoft Outlook

2019-09-30T16:23:05-04:00

There are several features in Outlook that I can only describe as “cool”, among which is the ability to create custom signatures for email. It’s a feature most people would think should be in a program like this, but that doesn’t make it any less cool in my eyes. And not only is it not hard to set up, but more than one signature can easily be made available. This is a very important business feature, if you think about it. Having business contact data in an email is, if nothing else, polite. First thing is to get to the dialog [...]

How to Set Up Custom Signatures in Microsoft Outlook2019-09-30T16:23:05-04:00

How to Select Precise Colors with Pickers and Libraries

2019-09-30T16:34:03-04:00

More than ever in today’s business world, having one’s company stand out from the competition is important; product branding, and the use of color in particular, is integral to this. Product branding is the term we normally use to describe a distinctive scheme of hue/shade/etc. choices, font choices, logo, etc. which give a company a unique “look”. Coca-Cola, John Deere, Five Guys (a burger chain), Panera—any company at all. So how can a company select—and use consistently—any of these, particularly color?      There are at least two methods we can use, at least in a majority of programs. One, which is [...]

How to Select Precise Colors with Pickers and Libraries2019-09-30T16:34:03-04:00

How to Create a Calendar in Project (and Why)

2019-10-02T16:31:22-04:00

When setting up a plan in Project, it’s very important to get the calendar organized first, before adding tasks, resources, assignments, or almost anything else. Why? Because a project timeline dictates many of the scheduling details, and the calendar controls the schedule. Getting to the dialog is easy—we click the Project tab, slide to the Properties group, and click Change Working Time. Once there, we usually want to click Create New Calendar at the top right, since copying an existing one and modifying it leaves the originals for later use. We can then change the name of the copy, say, to [...]

How to Create a Calendar in Project (and Why)2019-10-02T16:31:22-04:00

How to Use the Critical Path in Microsoft Project

2019-10-02T16:34:12-04:00

The use of the critical path in Project is a vital part of getting a project to finish on time. This is especially true if any juggling of the tasks, resources, and allocations has to happen once the majority of tasks are in place. It allows refinements to be done where they can have the best effect—what is sometimes called a “force-multiplier” (no Star Wars jokes, please); it simply means to make the most of what one has. The critical path is that series of tasks in which there’s no slack, time-wise. Think of a group of boxcars on a railroad [...]

How to Use the Critical Path in Microsoft Project2019-10-02T16:34:12-04:00

Using Dependencies, Lag, and Lead in MS Project

2019-10-02T16:38:59-04:00

Setting up dependencies, or task relationships, is an integral part of working in Project. But many newer users ask, Which relationship should I use, and What are these “lag” and “lead” things about? The choice of dependencies, as well as using the two other items, derives from the nature of the tasks. This is the one thing you have to bear in mind, because there’s no all-inclusive formula for determining these. That said, there are a couple of general pointers you can use to figure it out. If Task A has to be completely finished before Task B can start, such [...]

Using Dependencies, Lag, and Lead in MS Project2019-10-02T16:38:59-04:00

How to Create Custom Fields in Microsoft Project

2019-10-02T16:42:19-04:00

MS Project has a LARGE number of fields set up by default, for almost anything the program can track. But even with all the feedback the design team gets, they can’t anticipate everything a user might need to monitor during a project. So the team built in the ability to create, or rather modify, “custom” (unnamed generic) fields which are held in reserve for just this situation. One example might be which resources belong to which department in a company. The Group field in the Resource Sheet could be used to contain this information, but some users already employ it for, [...]

How to Create Custom Fields in Microsoft Project2019-10-02T16:42:19-04:00

How to Use Table Joins in Microsoft Access

2019-10-02T16:49:26-04:00

The use of table relationships in Access allows tables to cooperate in the use of data, but table joins, while they look similar, serve a different purpose. The relationships, which allow coordination while organizing data, nevertheless don’t directly affect, say, the results of a query. Joins do. A table join in a query allows for a filter effect. The thinking is that a query is a question one asks of the database. But redundant answers are no use, and a waste of space. So when we pull data from more than one table, frequently the case, we don’t want repeat answers, [...]

How to Use Table Joins in Microsoft Access2019-10-02T16:49:26-04:00

Using the Cost Tables in Microsoft Project

2019-10-02T16:53:56-04:00

The Cost Tables feature in Microsoft Project reflects a point which is particular to this program—it is, basically, time-sensitive. Since a business project takes a minimum, usually, of several weeks to run, the project must take time into account. And, no pun intended on this phrase, accounting for costs which might change during the run of the project is therefore an integral part of the resource data we can (and often must) enter. Fortunately, the first part is pretty easy. When creating a resource, one can (in the Resource Sheet) start by entering standard and overtime pay rates for a work [...]

Using the Cost Tables in Microsoft Project2019-10-02T16:53:56-04:00

Using the Three Normal Forms in Microsoft Access

2019-10-02T16:56:43-04:00

One of the more abstruse points of procedure in Access involves the three so-called normal forms. What, exactly, are they? If you think “guidelines”, or “protocols”, you’d be in the ballpark. When building a database, especially a relational database, there are some “streamlinings” which allow it to function more efficiently. Knowing the how is important, but the why can be too. "Why" is what I’d like to go over here. The first of the normal forms is basically this: In any field, in any record of the database, there should be one and only one piece of information. The simplest examples [...]

Using the Three Normal Forms in Microsoft Access2019-10-02T16:56:43-04:00

How to Import and Export Data in Access

2019-10-07T10:44:22-04:00

The good news is, the import and export processes in Access—and in general—are two sides of the same coin. And there really isn’t any bad news, other than making sure the data are set up in a way that Access can understand—namely, something row-and-column-ish when importing. There’s even an External Data tab in the ribbon, so it’s just a matter of knowing what kind of data you’re bringing in, or what format the recipient needs if you’re exporting. For an import, other than making sure you know where the data are, simply go to the External Data tab, and look in [...]

How to Import and Export Data in Access2019-10-07T10:44:22-04:00