Tag: excel 2016

Categories
Technology Training

Microsoft Excel 2016 Date and Time EDATE functions

In our last video, we showed how Microsoft Excel 2016 uses date and time functions. Now that you know the logic Microsoft uses to work with dates and times, you can use a variety of formulas to help make your job easier.

In this video, we will focus on a few more useful formulas that are the most common. Suppose you are creating a spreadsheet for all of your existing service contracts, and you want to keep track of when your contracts started and when they will end. Using the “EDATE” function you can tell Excel to add or subtract any number of days, weeks, months or years after a given date to produce the correct contract end date.

For example, if you are looking to find out when your contract will end one year from the start date, you would use the formula =EDATE(cell,12), which will add 12 months to the referenced date. Subsequently, if you have a five-year contract, you can multiply twelve months by five to get the correct date. That formula would be written as =EDATE(cell,5*12).

This formula is useful when calculating time in hours, minutes and seconds as well. Remember that dates are stored as whole numbers in Excel meaning that if you want to add 12 hours to a date, you would calculate the hours after the decimal point as 0.5.

There are countless ways to use these formulas and there are just as many applications that call for their use. Understanding the basic principles of how Excel uses dates and times will allow you to apply these formulas with ease. Once you get the hang of it, you can go to Microsoft’s support website to find many other useful functions.

Categories
Technology Training

Use Date and Time Functions in Microsoft Excel 2016

In today’s video, we are going to show you how to work with dates and times in Microsoft Excel 2016. First, we will show you the logic that Excel uses to calculate dates, and then we will show you how you can work with this logic.

It is useful to understand that Excel sees dates as numbers, adding one number for every day after the first of January 1900. The hours, minutes and seconds are stored as decimal values. Knowing this will help you be able to work with the date and time functions more easily.

The next step in working with dates and times is to know the “Today” function, which will bring up the current date in any cell, and the “Now” function, which will bring up the current date and time. If you need to reference a date in your spreadsheet, it is easier to type in the equal sign followed by “Today” and Excel will automatically populate the date for you.

Another common function is knowing how to calculate someone’s age using their date of birth in a formula. To do this you will need to know how to use either the “INT” or Integer function or the “YEARFRAC” function. Both will calculate the difference between a recorded date and the current date simply by using them in an equation.

Working with dates and times in Excel is very straight forward once you know how they are being processed. Knowing how to use functions to manipulate dates and times will help make you more accurate and efficient when working in Excel.

Categories
Technology Training

How to use Relative and Absolute Cell References in Microsoft Excel 2016

When working with a Microsoft Excel 2016 data table you may find that it is useful to define a constant outside of the data table rather than repeating it in every cell. Suppose you are trying to apply three different tax rates to a data table and you need to be able to adjust those tax rates without having to change them in every cell. Learning how to use cell references will allow you to do this by changing the reference rather than changing every row or column.

First, you will need to know the difference between defining a reference as relative or absolute. If for example, you are trying to add two columns together and you are using the SUM tool, you will take the first two number sets from each column and add the two together in a third column. This will automatically create an equation that you can extend to the rest of the numbers in the remaining column simply by placing your cursor over the sum of the first two numbers and dragging down the third column. This will extend the SUM equation to the rest of the numbers in the first two columns providing you the sum for the remaining number sets. This equation is using a relative constant because it changes with every set of numbers.

For the purpose of this video, we are going to focus on how to use an absolute constant. This is the equivalent of giving an equation an address to reference and whatever information you enter into that address will be reflected in the equation. In this case, the address is the X and Y access of the cell that you choose to use as your absolute cell reference. In our previous example, you would need to set up three cell references to correlate with the three tax rates. By labeling each tax rate A, B and C you can reference the tax rate in the data table allowing Excel to know which absolute constant to use in each equation.

Categories
Technology Training

Microsoft Excel 2016 Training “IF” Formula with “NOT” and “OR” Operators

In the last two Microsoft Excel training videos, we focused on using the “IF” formula, its Boolean operators and how to nest an IF statement inside of another IF statement. Today we will finalize our training on Excel IF formulas by reviewing how to use “NOT” and “OR” operators.

The NOT and OR operators are among the most common operators you will use in the IF formula. These operators allow you to create equations that will say “IF a workbook cell is NOT equal to X OR Y, then perform XYZ action.”

Let’s suppose you are needing to create a spreadsheet and you want a logic formula to adjust the prices of your products up or down according to their sales performance. An example of this would be if your yellow and pink products are not selling and you want to increase the price for the rest of your products while keeping the yellow and pink products the same price. These operators would allow you to say “IF the product is not Yellow or Pink, then increase the price.”

There are many ways to use these operators, and if you combine them with the operators that we reviewed in previous videos, you will find the IF formula to be quite powerful. With some practice, you will find that these operators are able to help you make most any calculator and will reduce your redundant workload.

Categories
Technology Training

Microsoft Excel 2016 Nested ‘IF’ Formula

In our last video, we covered how to use “IF” formula in Microsoft Excel 2016. The IF formula allows you to ask a cell in a spreadsheet if a statement is true or false. This basic formula will allow you to build complex calculators in Excel which will help you be more efficient in your work.

In this video, we will dive deeper into Excel IF formula’s to show you how to ask if more than one statement is true or false. A good example of this would be calculating A, B, C or D grades for a class of students. In this example, you would need to print a letter grade for a score that each student received on a test. Here, a letter would represent a score between two numbers and the IF formula would need to check which statement was true and which was false.

This is a slightly more complex use of the IF formula, however, with a little practice, you will find that this formula can save you from a lot of data entry and additional work.

Categories
Technology Training

How To Use Microsoft Excel 2016 “IF” Formula

One of the most powerful functions of Microsoft Excel 2016 is the logical formula. Especially the “IF” formula. The IF formula is a way for you to apply logic to your spreadsheet cells to make customized calculations just by entering data. It takes a little practice but once you get used to this function you will be able to build complex calculators simply by using the IF formula.
IF formulas look to see if a statement is True or False. If the statement is true the logical formula produces one calculation. If the statement if False then it produces another calculation.
In this video, we will review a few of the operators that allow you to use the IF logic effectively. These operators include equal to, greater than, less than, greater than or equal to, less than or equal to and the not equal to. Using these operators, you will be able to manipulate the IF formula for most any calculation.
This may seem complex but once you understand the format used to build this formula it is really quite simple. It will help to start by understanding what you want the calculation to do. The best way to do that is to write out what you want to calculate. An example of this would be “IF the number is greater than or equal to 100, print yes. Otherwise, print no.” Setting this formula for any cell will allow Excel to apply logic and make a quick calculation.

Categories
Technology Training

Import and Export Contacts in Outlook 2016

If you use Outlook for work, at some point you may need to import and export contacts from your account. Though it may seem complicated, it is actually an easy process if you use the tricks we lay out in this video.

Have you ever tried to import something into Outlook, only to have your file rejected, or your data imported incorrectly?  If you are trying to import contacts into Outlook from a file that isn’t using the same data fields that Outlook needs, you will get bogged down trying to make sure that your data is mapped correctly. If for example, your ‘first name’ data gets mapped to the ‘last name’ data field, your data will flow into Outlook incorrectly.

Before you import anything into Outlook, you will need to know what Outlook wants. Outlook considers a lot of data fields during the import process, and it can be difficult to determine which fields are necessary, or how to get them in the right format. For this reason, it is easier and more reliable to import data into Outlook using the spreadsheet it gives you.

To fix this problem, start by exporting a CSV file out of Outlook. This will give you a spreadsheet template with all of the fields that Outlook requires. Once you have exported a CSV file, you can begin entering the data you have regarding your contacts. If you carefully input your data into the correct fields, you will have a complete data set that is ready to be imported back into Outlook.

Importing is the same process as exporting. You will need to select the updated CSV file from your computer. Outlook will want to verify that the data fields match up, and this won’t be a problem since you are now using Outlook’s template. Now, you should be ready to start importing your contacts into Outlook.

If you follow these steps, you will soon be a master at importing and exporting contacts in Outlook!

To shortcut the export process, you can use this Excel Contacts Template that has all of the data fields that Outlook requires for contact import.

Categories
Technology Training

Microsoft Excel 2016 Spreadsheet Layout Design

When you start building spreadsheets in Microsoft Excel 2016 with hundreds of columns and rows, it can be confusing to understand what you are looking at. In this video we will show you a few simple tricks that will help you simplify your spreadsheets and make them easier to read.
Most spread sheets will have a constant which is a column or row that contains the same number. This could be an hourly rate for example that you are using to calculate employee wages. In this case you could set up a rule that will allow you to remove that column while still making the calculations. The added benefit of this is that you will be able to make adjustments to that number by changing it in one place rather then every cell. This will save you time and make the spreadsheet easier to read.
In addition to removing unnecessary information, you can make a lot more sense of a spreadsheet by applying some color coding and highlighting to information that is important. Sometimes it also helps to remove the grid lines if you are making this spreadsheet for a presentation.
Finally we will show you how to lock this information in place so that it can only be changed by an administrator. This is very useful if there is only one person who understands how the spreadsheet works. Changing the wrong information could upset the entire spreadsheet.

Categories
Technology Training

Microsoft Excel 2016 Calculations, Formulas and Spreadsheets

Setting up Microsoft Excel 2016 to make calculations is an easy and powerful feature to help you manage business, finance or whatever you may have a need for. In this video we will show you how to walk through some of the basic features of Microsoft Excel calculations and formulas by setting up a spreadsheet.
Excel is a vary powerful tool when you are making calculations that have a lot of moving numbers. We will show you how to set up a series of calculations that will allow you to enter numbers such as hours worked or amount spent and using basic formula, show you how Excel can process your input to make any calculation you would like.
Once you have the basic idea down, you will be able to make custom spreadsheets that are as simple or complex as you need them to be. If you find that you are having to make calculations on a regular basis using the same process, you can save yourself a lot of time by simply creating a spreadsheet to do the math for you. Once you have the spreadsheet set up, all you will need to do in enter the initial data and Microsoft Excel will do the rest.

Categories
Technology Training

Microsoft Excel 2016 Formatting Cells

A big part of learning how to work with numbers in Excel 2016 is learning how to format them so that Excel knows what your numbers are representing. There are many options in Excel such as general, number, currency, accounting, short date, long date, time, percentage, fraction, scientific and text. By telling Excel what your numbers are, Excel will have a format for those numbers that will make your life easier. More accurately, you are formatting the cell or range of cells that you are placing your numbers in. Any time you place a number in that cell it will appear with the format that you choose.
One example of using Excel in this way is the currency setting. When you turn on the currency setting for a cell, column or row you have enabled Excel to place two decimal places after the number you have entered, removing a few key strokes. It will also add in a comma for large dollar amounts and the currency symbol of your choice. This is ideal when you are working with money.
If you need to use Excel as a glorified calculator and you are working with percentages or fractions, Excel gives you options for that as well. Using the add, subtract, multiply and divide functions that we reviewed in the last Excel video, you can use percentages and fractions in mathematical equations.
If you are making a presentation in Excel and you would like to add in a note that isn’t effected by equations, you can use the text format. This will allow you to type anything you want into a cell without confusing Excel in the process.
There are many more options to choose from and when you become familiar with these options you will be able to use Excel much more effectively. In later videos we will review how to use the features to make more complex calculations.

Logos
Facebook Youtube Linkedin

Contact us

Master Services Agreement
Online Bill Pay
Clock
Copyright © eTop Technology. All rights reserved. | Privacy Policy
dell-partnerdirect-registered

CW Portal