Tag: Business Documents

If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you’re done.

When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

Here’s an example. To add the January numbers in this Entertainment budget, select cell B7, the cell immediately below the column of numbers. Then click AutoSum. A formula appears in cell B7, and Excel highlights the cells you’re totalling.

Press Enter to display the result (95.94) in cell B7. You can also see the formula in the formula bar at the top of the Excel window.

AVERAGEIF(range, criteria, [average_range])

The AVERAGEIF function syntax has the following arguments:

• Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
• Criteria Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
• Average_range Optional. The actual set of cells to average. If omitted, range is used.

Remarks

• Cells in range that contain TRUE or FALSE are ignored.
• If a cell in average_range is an empty cell, AVERAGEIF ignores it.
• If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
• If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
• If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
• You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
• Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:

Average: Which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

Median: Which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.

Mode: Which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

Example

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect.

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

For example, =IF(C2=” Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).

Common problems

SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

range   Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

Criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included – a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

For example, criteria can be expressed as 32, “>32”, B5, “3?”, “apple*”, “*~?”, or TODAY().

sum_range   Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

Sum_range should be the same size and shape as range. If it isn’t, performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range. For example:

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

=COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

• =COUNTIF(A2:A5,”London”)
• =COUNTIF(A2:A5,A4)

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Auto completion of series

Enter the first number in the top cell. Hover until you see the Fill Handle and with the RIGHT mouse button, drag the Fill Handle until you have selected the cells to autofill.

Autofilling Via the Excel Ribbon

The Series window can be reached via the ribbon in Excel version 2007 and newer.

• Enter the first number of the series. Then select that cell and the cells that you want to auto fill.
• Click the Fill button located on the Editing section of the ribbon’s Home tab as shown in the first image.
• A drop-down menu appears as shown in the second image. Click Series and the Series window appears as shown below.
• Enter your step value and any other values on the Series window and click OK.

Through Drag

• Select one or more cells you want to use as a basis for filling additional cells.
• For a series like 1, 2, 3, 4, 5…, type 1 and 2 in the first two cells. For the series 2, 4, 6, 8…, type 2 and 4.
• For the series 2, 2, 2, 2…, type 2 in first cell only.
• Drag the fill handle Fill handle.
• If needed, click Auto Fill Options Button image and choose the option you want.

Sort and filter

Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.

Sort text

1. Select a cell in the column you want to sort.
2. On the Data tab, in the Sort & Filter group, do one of the following:

• To quick sort in ascending order, click A to Z command in Excel that sorts A to Z or smallest number to largest (Sort A to Z).
• To quick sort in descending order, click Z to A command in Excel that sorts Z to A or largest number to smallest (Sort Z to A).

Filtering Data

The filter feature applies a drop-down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

• Go to the Data tab on Excel ribbon
• Select the Filter tool
• Select Eastern Company from the dropdown menu
• Select Dylan Rogers from the Salesperson dropdown menu

Charts

Create a chart

• Select the data for which you want to create a chart.
• Click INSERT > Recommended Charts.
• On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data, and click any chart to see how your data will look.
• If you don’t see a chart you like, click All Charts to see all the available chart types.
• When you find the chart you like, click it > OK.
• Use the Chart Elements, Chart Styles, and Chart Filters buttons, next to the upper-right corner of the chart to add chart elements like axis titles or data labels, customize the look of your chart, or change the data that is shown in the chart.
• To access additional design and formatting features, click anywhere in the chart to add the CHART TOOLS to the ribbon, and then click the options you want on the DESIGN and FORMAT tabs.

Formulas are the backbone of most advanced calculations within Excel. Think of a formula as an equation; it’s a series of steps that lead to a final output. You can tell Excel that you’re entering a formula in Excel by starting your input with the equals sign (=). For example, try entering the following into a cell:

Formulas in Excel use the standard mathematical order of operations to evaluate. As such, operations within formulas are processed in this order:

• Parentheses
• Exponents (^)
• Multiplication (*)
• Division (/)
• Addition (+)
• Subtraction (-)

All Excel formulas begin with an equal sign (=).

After the equal symbol, you enter either a calculation or function. For example, to add up values in cells B1 through B5, you can either:

• Type the entire equation: =B1+B2+B3+B4+B5
• Use the SUM function: =SUM(B1:B5)

Press the Enter key to complete the formula. Done.

Elements of Microsoft Excel formulas

When you make a formula in Excel, you can use different elements to supply the source data to the formula and indicate what operators should be performed on those data. Depending on the formula type that you create, it can include any or all of the following parts:

Constants: numbers or text values that you enter directly in a formula, like =2*3.

Cell references: reference to a cell containing the value you want to use in your Excel formula, e.g. =SUM(A1, A2, B5).

To refer to data in two or more contiguous cells, use a range reference like A1:A5. For example, to sum values in all cell between A1 and A5, inclusive, use this formula:

=SUM(A1:A5).

Names: defined name for a cell range, constant, table, or function, for example =SUM(my_name).

Functions: predefined formulas in Excel that perform calculations using the values supplied in their arguments.

Operators: special symbols that specify the type of operation or calculation to be performed.

Special paste

Paste menu options

• Select the cells that contain the data or other attributes that you want to copy.
• On the Home tab, click Copy Copy icon.
• Click the first cell in the area where you want to paste what you copied.
• On the Home tab, click the arrow next to Paste, and then do any of the following.

Paste options

Operation options

The Operation options mathematically combine values between the copy and paste areas.

Freeze pane

To keep an area of a worksheet visible while you scroll to another area of the worksheet, go to the View tab, where you can Freeze Panes to lock specific rows and columns in place, or you can Split panes to create separate windows of the same worksheet.

Freeze the first column

• Select View > Freeze Panes > Freeze First Column.

            The faint line that appears between Column A and B shows that the first column is frozen.

Freeze the first two columns

1. Select the third column.
2. Select View >Freeze Panes > Freeze Panes.

Freeze columns and rows

1. Select the cell below the rows and to the right of the columns you want to keep visible when you scroll.
2. Select View > Freeze Panes> Freeze Panes.

Unfreeze rows or columns

• On the View tab > Window > Unfreeze Panes.

Formatting text or numbers can make them appear more visible especially when you have a large worksheet. Changing default formats includes things like changing the font colour, style, size, text alignment in a cell, or apply formatting effects.

All cell content uses the same formatting by default, which can make it difficult to read a workbook with a lot of information. Basic formatting can customize the look and feel of your workbook, allowing you to draw attention to specific sections and making your content easier to view and understand. You can also apply number formatting to tell Excel exactly what type of data you’re using in the workbook, such as percentages (%), currency ($), and so on.

If you want text or numbers in a cell to appear bold, italic, or have a single or double underline, select the cell and on the Home tab, pick the format you want:

Change font style, size, color, or apply effects

Click Home and:

• For a different font style, click the arrow next to the default font Calibri and pick the style you want.
• To increase or decrease the font size, click the arrow next to the default size 11 and pick another text size.

The Internet (or internet) is the global system of interconnected computer networks that uses the Internet protocol suite (TCP/IP) to communicate between networks and devices. It is a network of networks that consists of private, public, academic, business, and government networks of local to global scope, linked by a broad array of electronic, wireless, and optical networking technologies. The Internet carries a vast range of information resources and services, such as the inter-linked hypertext documents and applications of the World Wide Web (WWW), electronic mail, telephony, and file sharing.

The origins of the Internet date back to the development of packet switching and research commissioned by the United States Department of Defense in the 1960s to enable time-sharing of computers. The primary precursor network, the ARPANET, initially served as a backbone for interconnection of regional academic and military networks in the 1970s. The funding of the National Science Foundation Network as a new backbone in the 1980s, as well as private funding for other commercial extensions, led to worldwide participation in the development of new networking technologies, and the merger of many networks. The linking of commercial networks and enterprises by the early 1990s marked the beginning of the transition to the modern Internet, and generated a sustained exponential growth as generations of institutional, personal, and mobile computers were connected to the network. Although the Internet was widely used by academia in the 1980s, commercialization incorporated its services and technologies into virtually every aspect of modern life.

Most traditional communication media, including telephony, radio, television, paper mail and newspapers are reshaped, redefined, or even bypassed by the Internet, giving birth to new services such as email, Internet telephony, Internet television, online music, digital newspapers, and video streaming websites. Newspaper, book, and other print publishing are adapting to website technology, or are reshaped into blogging, web feeds and online news aggregators. The Internet has enabled and accelerated new forms of personal interactions through instant messaging, Internet forums, and social networking services. Online shopping has grown exponentially for major retailers, small businesses, and entrepreneurs, as it enables firms to extend their “brick and mortar” presence to serve a larger market or even sell goods and services entirely online. Business-to-business and financial services on the Internet affect supply chains across entire industries.

The Internet has no single centralized governance in either technological implementation or policies for access and usage; each constituent network sets its own policies. The overreaching definitions of the two principal name spaces in the Internet, the Internet Protocol address (IP address) space and the Domain Name System (DNS), are directed by a maintainer organization, the Internet Corporation for Assigned Names and Numbers (ICANN). The technical underpinning and standardization of the core protocols is an activity of the Internet Engineering Task Force (IETF), a non-profit organization of loosely affiliated international participants that anyone may associate with by contributing technical expertise. In November 2006, the Internet was included on USA Today’s list of New Seven Wonders.

The word internetted was used as early as 1849, meaning interconnected or interwoven. The word Internet was used in 1974 as the shorthand form of Internetwork. Today, the term Internet most commonly refers to the global system of interconnected computer networks, though it may also refer to any group of smaller networks.

When it came into common use, most publications treated the word Internet as a capitalized proper noun; this has become less common. This reflects the tendency in English to capitalize new terms and move to lowercase as they become familiar. The word is sometimes still capitalized to distinguish the global internet from smaller networks, though many publications, including the AP Stylebook since 2016, recommend the lowercase form in every case. In 2016, the Oxford English Dictionary found that, based on a study of around 2.5 billion printed and online sources, “Internet” was capitalized in 54% of cases.

The terms Internet and World Wide Web are often used interchangeably; it is common to speak of “going on the Internet” when using a web browser to view web pages. However, the World Wide Web or the Web is only one of a large number of Internet services, a collection of documents (web pages) and other web resources, linked by hyperlinks and URLs.

Surfing the web

Alternatively known as web surfing, surfing with computers describes the act of browsing the Internet by going from one web page to another web page using hyperlinks in an Internet browser. The term “surfing” was first coined by Mark McCahill.

The term comes from “TV channel surfing,” but instead of clicking on the buttons of the remote, the user jumps from page to page by clicking on the links in the Web page.

Surfing is an activity that started with the advent of the World Wide Web. With hypertext links, users can go not only from one part of a document to another, but also from one document to another, including those located in remote sites. Surfing is a favorite pastime for millions of people around the world who have access to the Internet. Many users are hooked on it, spending countless hours doing casual searches or other online activities. Some do it to kill time.

Online aggregation services allow you to surf the Internet. Some of the most popular places include Reddit, StumbleUpon, and Pinterest. Also, you can use a search engine, such as Google, to search for anything that interests you.