Microsoft Office 2016: The Complete Guide (2015)

PERFORMING VARIOUS FUNCTIONS IN EXCEL FOR MAC

Worksheets

A worksheet begins with row number one and column A. An Excel worksheet is a single spreadsheet that contains cells organized by rows and columns. Microsoft allows you to switch between worksheets by clicking on the worksheets tab on the bottom of the Excel window. Each cell can contain a number, text or formula. A cell can also reference another cell in the same worksheet or a different workbook.

Multiple Worksheets

Excel’s multiple worksheet data entry and data management services allows users to examine and deduce data which will allow for better decision making. You can analyze your data quicker and easier if you have the data organized and easily accessible. It is possible to have different sets of data in each worksheet. For example, users can arrange their workbook by having different worksheet denoting different areas of business concern. Worksheets can illustrate purchases, sales, income, expenditure, budgeting and inventory control data on different sheets.

The capacity to have multiple worksheets in an Excel workbook allows you to alternate between worksheets easier and faster simply by checking the tab at the bottom of the workbook and selecting the sheet you want.

How to Add and Rename Worksheets in Mac 2016

To insert a new worksheet:

Check on the plus sign next to the last worksheet tab. A new blank worksheet will be created. You may desire to insert additional worksheets.

Alternatively, you can click on "Sheet" under "Insert" on the top menu bar of Excel.

To change the name of a worksheet right click on a worksheet tab, select "rename" and type in a new name followed by the Enter key.

You can click the sheet tabs to navigate within your workbook as well as add additional sheets when the need arises.

How to insert Rows in Microsoft Excel 2016 for Mac?

Rows are cells that go horizontally from left to right (or side to side) in an Excel worksheet. A row is illustrated by the number to the extreme left in the row header. There are some one million rows in each Excel worksheet which will give you a lot of leg room to input large amounts of data. Here is how you insert a row.

Use your mouse to Right click on the place where you want to insert the row. This more accurately would be on a number which is a part of your workbook which is in the utmost left hand corner.

When you right click a drop down box will pop up.

Select insert and a new row will appear.

If you want multiple rows block a number of rows (this could be consistent with the number of rows you desire) and then repeat the steps above.

How to insert Columns in Microsoft Excel 2016 for Mac?

Columns run opposite to rows, they go vertically in a worksheet. Each column is identified by a letter in the column header unlike a number that is used for rows. Columns are usually used to identify your headings for your worksheets.

Here is how you insert a column.

Use your mouse to Right click on the place where you want to insert the column. This more accurately would be on a letter which is a part of your workbook which is at the top of your workbook.

When you right click a drop down box will pop up.

Select insert and a new column will appear.

If you want multiple columns block a number of columns (this could be consistent with the number of columns, you desire) and then repeat the steps above.

Cells

A cell is the basic storage unit for data in a spreadsheet program, created by the intersection of a horizontal row and a vertical column. A cell may be formatted to contain specific type of data’s, like labels, numbers, text, date, formula and so on. A cell that is formatted for numbers cannot contain text in it, in the same way a cell set for text will not accept numbers. The location of cell of data is identified by a cell reference.

Reference Cell

This is the column row ID of a cell. In cell references the column name appears before the row name. So cells are always referenced as A1 or M209 for example.

Descriptive Statistics

Descriptive statistics are the tools used, to describe the patterns observed within the dataset. It outlines the different ways of summarizing the data within a spreadsheet. When choosing a method to summarize your data, it is imperative that all important features of the data are maintained. This is so because if your information is lost, then the data no longer accurately represents the sample from which it was collected. This may also cause the results to be inaccurate.

Sorting Data

Sorting is the process of rearranging a collection of items within a spreadsheet. Items may be arranged in ascending order (A-Z or 1-100) or descending order (Z-A or 100-1) using some criterion also referred to as ordering. It is important to note that items can also be placed in categories. This process is called categorizing. When categorizing items, they should have similar traits and characteristics. Microsoft Excel 2016 for Mac allows its users to sort:

Numbers and text,

Weekdays and months, or

Items from custom lists that you created.

Sorting can also be done by using font color, cell color, or icon sets. Microsoft Excel 2016 for Mac can sort a column which will rearrange the rows of the column with the worksheet. You can also sort multiple columns or a table, which is will rearrange all the rows depending on the contents of each particular column.

It is important that the columns contain the same type of data that is text, number, date and so on. The columns should not contain data that are stored as numbers and numbers that are stored as text. If this happens with your data, the result will be incorrect. Numbers that are stored as text must be displayed left aligned and not right. You will have to format the cell in a particular column.

2. How to Sort a Column

Steps to sorting a column:

Click a cell in one of the columns that you wish to rearrange.

Data in neighboring columns will be sorted based on the data in the column that you have selected.

On the Data tab, click on Ascending or Descending.

To arrange data in the form of the lowest values to the largest value of the column click A to Z.

To arrange in descending order which is where the highest values appear first in the column and the lowest last in the worksheet click Z to A.

Benefits of Sorting Data

Sorting helps to make searching for items much easier and it saves time. Sorting also keeps data that are similar close to each other. In the spreadsheet of a company, information regarding sales would have been sorted and stored together. In the same way purchases, clients and Customer information would have been stored together. This helps to provide easy access to information and speed up the decision making process in the company.

Workbook Formatting

Formatting of Cells

Microsoft Excel 2016 for Mac has the power to format numbers which improves the readability in worksheets and workbooks. Formatting is applying styles to a cell which will say what items will be accepted in the specific cell and also decide how the data inserted in that cell will be presented.

Formatting Numbers

The main purpose of Microsoft Excel 2016 for Mac is to display numbers and calculations, numbers must be formatted so that they can appear more logical, constant and clear. Formatting a cell for example, displaying currency with previous dollar signs, putting commas in large numbers, and having percentage signs after percentages are a few cases within which formatting number cells can prove helpful.

Carrying Out Calculations, Filtering and Look up

Calculations in Excel

Microsoft Excel 2016 Mac users will be pleased about the new data operation improvements. These improvements include the ability to take a formula, drag and drop to create the same formula in a number of columns based on the first cell in the selection. The Formula Builder helps the user learn how to use Excel or use it without having to know all the formulas. The builder creates difficult formulas for the user.

A Formula refers to the equations that carry out mathematical operations on values in your spreadsheet. An equal sign (=) is the first thing to be entered to start all formula. The equal sign in a cell communicates to the processor that you want to perform the sum of a calculation. A simple formula can be made using numbers and mathematical operators. For example, in the formula =21+52, you are adding two numbers. You can also insert =21+52*2 which will multiply the last two numbers and then add the first number to the result.

You can use values already present in other cells to create a formula. Here is how it works, you can add the contents of cells A2 and the contents of cell B2. In order to do this, you will need to insert in the formula bar or in the cell where you want the results to be displayed type =A2+B2. You can also work will more difficult formulas, such as the addition of hundreds of values, this is very much possible with the use of a range. A range is a sequence of two or more adjoining cells. This is can be done however by grouping multiple cells reference like (A2:A376). This is where you would use the sum function so the formula would be =Sum (A2:A376). A colon is used to separate the range’s beginning cell and the ending cells. The results will automatically change to reflect any change or changes in any part of the data that is a part of that cell.

Enter a Formula that Refers to Values in Other Cells

In using Microsoft Excel 2016 for Mac there are procedures that should be followed when entering your formula. An example is, you may want to find the difference between your total purchases and sales. This task can be accomplished in a number of different ways.

The Sum function can be used to find the total sales and purchases and then use the subtraction formula to find the difference.

Or picture the values for sales and purchases occupying cells C2 to C3 and D2 to D3 respectively.

Type in, =Sum (C2:C3) and =Sum (D2:D3), this formula will return the results showing the total sales and purchases for the specific period intended.

To find the difference, place the results in cells C5 and D5, enter = C5-D5, this will show the difference between total sales and total purchases. The following Mathematical operators such as + (addition), - (subtraction), * (multiplication), / (division) and <> (less than or greater than) are main parts of formulas in Excel.

Operators in Formula

What is also important is that Microsoft Excel 2016 for Mac uses Arithmetic within operators of its Formula. They are used to execute arithmetic between variables and/or values. Arithmetic operators take numerical values as their operands and return a single numerical value. The most frequently used and chief arithmetic operators are addition, subtraction, multiplication, and division. Microsoft Excel 2016 for Mac uses different symbols to represent each, a (+) is used to represent addition, (-) for subtraction, an asterisk (*) for multiplication and a forward slash (/) for division. In order to manipulate your data and tell your computer how to do so operators are important part of your formulas to carry out this task.

Parentheses in Formula

Parenthesis formulas in Microsoft Excel 2016 for Mac determines the order of operation. Parentheses are also called brackets. Microsoft Excel 2016 for Mac sticks to the normal order of arithmetic operations, which gives more mass to multiplication and division because their operations are performed first, followed by addition and subtraction. In the order of preference, multiplication and division are performed first but in the case where there is a bracket (parentheses) the operation in the brackets are performed first. Parentheses are basically used to change the order of operations.

Enter Formulas with Functions

In a spreadsheet that has in a range of numbers you must follow these steps:

Click the empty cell where you want the formula results to appear.

Type an equal sign followed by a function, for example =MIN, =AVG, =MAX., which is short for minimum, maximum and average. Maximum requires the largest minimum the smallest and average would find the average of the numbers in a specified range in the spreadsheet.

Open brackets, click on the range of cells that you want to be in the formula.

Then close the bracket and press Enter. When the formula is entered in to the cell it also appears in the formula bar.

Why is Summarizing Important?

A Summary of large chunks of document is important because it gets right to the point and information is less time consuming to find. When you summarize your data important information is articulated in a more coherent manner. Data that is summarized makes it easier to compare information. A company may want to compare data from profit department with data from losses and in such a case comparing individual profit against individual loss may not be accurate. However, a summary of the total profit against summarized losses may reveal better results. Summarized data allows businesses to better manage their activities and transactions. Excel users can benefit from summarized data for their personal activities, it is not just a software used companies and large organizations. It helps them to express important ideas and find main ideas.

A specified variable is better controlled when working with a small sample. Your information that has been composed is much easier to analyse and present. When you have a large data set, summarizing that information to the frequency of how often something occurs or the average number of times and event occurred is a better method of presenting that information. Although summarizing data can be very useful you must be aware that your data could lose some of its possessions which will affect the accuracy of the document and how it compares to other variables.

It is very important that we summarize data because it makes it easier for readers to read and understand, therefore it is imperative to find a way of summarizing the main aspects to ensure the distribution of the data without losing any of its main features. The method used to condense or explain the collected data is known as Descriptive Statistics. These statistics points out the important patterns within the dataset without losing any important features. The advantages and disadvantages should be taken into consideration when choosing a method to summarize your data whether it is categorical or numerical. The way in which you distribute the data is also very importance.

Beneficiaries of Summarized Data

Summarized data can be used for many different reasons:

It can be used in large or small organizations and also for households or individual needs. For example, in the household to track the family’s financial activities over a period of time.

Microsoft Excel 2016 for Mac can be used to summarize data for the amount of time spent on activities during a specified period.

Companies can use it to monitor client’s activities, check their supply schedules and to design a purchasing plan.

Filter

This is a method of summarizing that is mainly focused on how the data is viewed. It allows you the privilege of selecting specific data to be reviewed. It provides a distinctive view even though it doesn’t summarize the data mathematically. Filtering can be further done after the data has been filtered by using the Auto Sum to summarize the visible data, as follows:

Apply a filter by selecting the data range ([Shift]+[Ctrl]+8).

Click the Data tab and click Filter in the Sort & Filter grouping.

Display your subset once the filter is in place

When you get to this point you have a full summary of your data, but if you want to go a step further you can by using AutoSum.

The SUBTOTAL function will be substituted once auto sum recognizes that an active filter exists.

Subtotals

Sorting and filtering are easy to apply, additional task are more complex. The subtotal Excel's feature summarizes values depending on other related value that changes. This is why Subtotal highly depends on sorting. If a company wants to look at the number of deliveries made for a particular day. Data range in the column must be sorted that numbers or values are in the same way. If your data is not in the correct order you cannot skip the particular step!

Click inside the data range and press [Ctrl]+[Shift]+8 to select the sorted data range.

Click on the Data tab.

Click Subtotal in the Outline grouping.

Choose Subtotals from the Data menu.

Conditional aggregate

The conditional aggregates functions act upon values that meet a specific condition. The representation of this function is SUMIF (). So if a company wishes to identify the deliveries made on a particular day and which employee made the delivery then conditional aggregate is the ideal method to use when summarizing this information.

SUM

Microsoft Excel 2016 for Mac makes performing some functions that would be very time consuming and exhausting very easy. Think about being in charge of calculating the total deliveries for a month by taking each delivery day by day and adding them. Microsoft Excel 2016 for Mac’s Sum function makes it extremely easy to do calculate this using one formula, the SUM function. The SUM function is used to calculate values in your worksheets. The number 1 argument is to identify the conditional values when using the SUM function. This is the range of numbers in a cell that you want to be added together. You can enter a number of other numbers, separated by commas, in a single SUM formula. You can build a SUM formula, for example, that total numbers in several different ranges such as: =SUM (A2:C13, Sheet2! B5:B9, Sheet4! B6:B15)

Multiple Conditional Aggregates

When you have numerous conditions, use SUMIFS (), AVERAGEIFS (), and COUNTIFS (). Using the example above, we can add a second condition, namely:

Dynamic multiple conditional aggregates

The criteria presume the equality operator (=). This criteria is flexible, dynamic multiple conditional aggregates items can be aligned together that are unfamiliar so that they can be treated as a unit.

The Consolidate Feature

The purpose of the Consolidate feature is to merge and summarize data from multiple workbooks, it can also be used to summarize data in the same file. Often time this feature has been overlooked. You must first setup the feature;

The column(s) you're summarizing must have heading(s).

A range name must be assigned to the column(s) to be summarized.

The values you're summarizing by must be to the left of the one to be summarized.

After those steps are completed, carry out this feature as follows:

Select the top-left anchor cell for summary to be displayed.

Click the Data tab and click Consolidate in the Data Tools grouping.

Select Consolidate from the Tools menu.

Click the Function drop-down to see what's available in the resulting dialog

Choose the appropriate function.

In the Reference control, enter the range name of the data your summarizing

Delete references in the All References list if you see any.

Click the most suitable options in the Use Labels in section — has both Top Row and Left Column.

VLOOKUP

Finding an item in a large spreadsheet is not difficult when using VLOOKUP. VLOOKUP is used when you need to find things in a table or a range by row. For example, looking up an employee's last name by the employee address, or finding their address by looking up their last name (in the same way you would in a directory).

Minimum

The Minimum function searches your dataset and gives back the smallest value within the variable you had selected.

Maximum

The Maximum function in Microsoft Excel searches your dataset and returns the highest or largest value within the variable or range you had chosen.

Average

In Microsoft Excel AVERAGE function returns the average of the variable or numbers in a range selected.

The Mean

Huge spreadsheets can sometimes make it difficult to analyse data, as it would be too repetitive and time consuming to look to each item individually. Using the mean or average is one of the most common ways to look about your sample. Depending on the information you have gathered the mean makes it easy to make generalizations about your sample.