Subtracting in Excel

Subtracting in Excel

If you are trying to subtract numbers while in Excel, you first need the equals sign = and place – (minus) between two or more values. We can call this a formulas and particular and care needs to be taken when entering it. Order is everything. When it comes to Excel formulas there a few things you need to remember:
In order to complete the formula, you must hit Enter or Tab on the keyboard

Subtracting in Excel

To know how to add Columns and Rows to a Table in Excel, click at http://www.whiskydemalta.com/add-columns-rows-table-excel/

Refer to Cells rather than numbers

One option is to enter numbers directly into a formula but a better and more preferred way is to enter your data into the worksheet cells and then use the references of those cells for your formula.

Subtracting in Excel

The benefit to using references instead of raw data, is because you may need to change the data at a later time and it is easier to do. You simply replace the information and it is transferred to the formula without having to re-write the entire thing. Everything is done more automatically and efficiently this way. You may also choose to mix raw data and cell references, which is also completely acceptable (as in row 4 of the example). To read more information about Excel 2016 click here

Using Brackets when dividing

We all remember that from math class that order needs to be followed when summing/dividing/multiplying. Similarly, Excel also has an order of operation when evaluating equations to determine which part of the formula to solve first. Orders of operation can be altered by simply adding brackets (parenthesis):

Subtracting in Excel

What is VLOOKUP and How or Why would I use it?

What is VLOOKUP and How or Why would I use it?

VLOOKUP is a highly useful tool, especially when dealing with a large spreadsheet, that allows the user to quickly find specific data. VLOOKUP stands for Vertical Lookup and the name says it all. This formula searches vertically down the first column within the range listed in the formula to locate the designated search word or number. Once the desired data field is found it then moves across the row based on the second range listed in the formula. The formula then lists either a TRUE or FALSE. A TRUE value will return approximate values whereas a FALSE value will only return the exact value results. To know How to Use VLOOKUP in Excel, click here

It sounds complicated but let’s break it down a little more using the spreadsheet below.

What is VLOOKUP and How or Why would I use it?

(Two column spreadsheet, Column 1 = Item, Column 2 = Number, Items are A to H, Numbers are 1 to 8)

Say someone wants to know the Number associated with Item E. Glancing at the spreadsheet it is easy to see that the Number would be 5 but imagine a spreadsheet with over 10,000 Items in it. How would one quickly search to not only find E but to locate the Number? This is where VLOOKUP comes in extremely handy.
1. Click in any cell without data entered to type the formula.
=VLOOKUP(
2. The first argument you will enter will be the value you are looking for. Always remember to place a comma after each argument.
=VLOOKUP(“E”,
3. The second argument is the cell range in which the data is listed. For this example, the cell range is A2:B9.
=VLOOKUP(“E”,A2:B9,
4. The third argument is the number of the column where the result is entered. In this example, we are trying to find the number for E and the numbers are listed in the second column so enter a 2 as the third argument.
=VLOOKUP(“E”,A2:B9;2,
5. The fourth and final argument as mentioned above is whether you would like approximate matches. In this example, we only want the exact match returned so enter a FALSE as the fourth argument and close out the parenthesis and hit return and it should give you the answer of “5”.
=VLOOKUP(“E”,A2:B9,2,FALSE)
To find the number for a different item you would simply replace the first argument with the desired search. Example: =VLOOKUP(“A”,A2:B9,2,FALSE)

To get the information about use of IF function in Excel, click at http://www.whiskydemalta.com/use-excels-function/
If the search is across multiple Sheets or Workbooks.
In this case the easiest way is to open both workbooks and begin typing your VLOOKUP formula. When you reach the second argument, the table array, click into the other sheet or workbook and select the range from there. However, you can manually enter the formula if you know the values you want entered. An example would look something like =VLOOKUP(“A”, Sheet3!A2:B17,2,FALSE)
As you can see the formula shows that “Sheet3” is now the table range. Likewise, the Name of the Workbook would be listed there if the range spanned across multiple workbooks.
So, what are a few examples of when this formula would be most useful? A Contact list is a prime example, allowing the user to quickly locate a person’s name and phone number or address. VLOOKUP would also be useful to quickly scan an inventory list and locate the item and how many are currently in stock. This is a very useful formula for many Spreadsheet uses. To read more about basic excel formulas, click here.

How to Make a Pivot Table Report in Excel 2016

How to Make a Pivot Table Report in Excel 2016

Beyond the rows and columns of a spreadsheet Excel offers the ability to create detailed reports. Reports can be formatted with borders, clip art, font formatting as well as number formatting. Visually a report is more appealing that the very ordinary view of the spreadsheet. Excel reports can be very basic or extremely complex. A basic report can be created rather quickly depending upon the amount of formatting. To create a basic report, it’s best to use a PivotTable. To learn about adding new rows and columns in Excel click at http://www.whiskydemalta.com/add-columns-rows-table-excel/

If using a workbook with data already entered the first step you should take is to organize and correct any data within the spreadsheet. Double-check that there is only one record in each row, one column per category, and one column per metric. For example, you will want to total all costs into one column versus separating costs out across several columns. Once the data is sufficiently organized create a report by following three simple steps.read more info on excel by clicking here

  1. Highlight all the columns and rows that you want included into your report. Click into a cell and holding down right mouse button move the mouse over all cells to highlight them.

  1. Select the INSERT tab at the top and click on the PivotTable button. A window will pop-up showing the selected table range. There’s also the option to Use an External Data Source. Next Choose whether you would like the PivotTable to be placed within an existing workbook or create a new workbook. Click OK.

  1. The PivotTable Field List will appear on the right that allows you to select the Rows, Columns, Filters, and Sum Totals for your report. Customize by dragging the fields from down into the areas

The Rows box will place the fields on the left side of the report. The Columns box will place the fields across the top of the report. The data you would like to summarize should be placed within the Values box. Each item you placed within the Values box can then be summarized by sum, count, average, minimum, maximum, or other calculations (In the screenshot below, I have chosen SUM)

That’s it. You now have a clear and informative report made from the raw data in your workbook. If you should choose to format your report you can choose colors by changing the Theme. Select Page Layout tab from the top, Click on Themes, and select one. Another option is to personally select the color scheme, fonts, and effects within the Themes tab. Within the Insert tab at the top you can also insert Charts, Headers, Footers, and a text box. To insert a chart based upon your report you can choose from the Recommended Charts, select any of the several options in charts, or choose the PivotChart.

Right clicking on the PivotTable will allow you to format the cells, change the number formatting, change the way the values are summarized, and other options. Another option is to choose the PivotTable Tools at the top.

There you can choose between ANALYZE or DESIGN (in Excel 2016) to make changes to your PivotTable. The ANALYZE tab allows you to change the name of the PivotTable, choose the Active Field, and adjust several settings. The DESIGN tab is for choosing what will be seen on the PivotTable for example whether the rows will be banded or whether Subtotals or Grand totals will appear on the Report.

To update or add new data to your PivotTable you need to make the changes in the original spreadsheet first and save. Open your workbook that has the PivotTable within it and right click anywhere in the PivotTable range. Select Refresh and the PivotTable should automatically show the updated or changed data. If you have multiple PivotTables that need to be updated select any cells in any of the PivotTables you are refreshing. At the top go to PivotTable Tools, select Analyze, click on Data and Click on the Arrow under the Refresh button and then click on Refresh All. To get further information about Pivot Table in Excel, click here

To delete a PivotTable that you no longer need or want, simply select the entire PivotTable by clicking in the top left of the PivotTable and either pressing DELETE on your keyboard. Deleting a single PivotTable will not change the data, charts associated with the data or any other PivotTables.

How To Use Excel's If Function.

How To Use Excel’s If Function.

If Function is one of the commonly used excel function that allows one to make and come up with a logical comparison between a value and the results you expect to get. The If Function may end up giving either two results with one containing your accurate results and the other answer may be wrong. If Function can be used in businesses to determine the conditions that can lead to doing a particular activity such as a transaction or a production or to determine the accuracy of a marketing report. It is important that you use all your logic when applying this function to attain the expected results.

How can one use IF function in excel?

• Open the excel spreadsheet and select a blank cell.

The first step requires you to open the excel spread sheet and choose any blank cell on the worksheet.

• Type equals and IF.

How To Use Excel's If Function.

After selecting the cell then type in the equals sign(=) followed by the word IF.

• Type in open brackets.

Once you key in the equals sign and IF function, the other step requires you to type the open brackets. This is crucial as it directs the application to work on the edited numerical.

• Select the cell where you want the results to be displayed.

You need to choose where you want the results to be shown for example you can select cell A or Cell B according to the problem you are working on.

• Key in the stated value and include a comma.

Go back to the cell which you edited the equals sign and open bracket and then add your values that you want to work on. This is followed by adding a comma at the end of the value. One vital detail to note though is that some languages require you to use a semi colon instead of commas to display the results.

• Record the statement if you find the condition is not satisfied.

If you don’t find a true answer, then you can write a statement to show that the value is either greater, equals to or less than equals to. Learn about subtract in excel by clicking at http://www.whiskydemalta.com/subtracting-in-excel/

The importance of IF function.

• Excel’s IF function helps in determining if a particular statement is met, the returns of the value are valid or if the results are false. In other words, it helps one to know if the condition they are working on gives a correct answer of which is displayed on the spreadsheets if the reply is not correct the If function provides different predefined values.

• IF Function does not give two results you either get the true or false answer, and it also helps you notice in the case where you are working on an invalid expression.

• The excel function is also useful for large companies handling large reports for employees or in sales departments as it helps in recording and confirming if the figures included in the report are true or false. The function gives you one result that may be either True of false depending on the expressions that you use to work out the value.

How to Add Columns and Rows to a Table in Excel

How to Add Columns and Rows to a Table in Excel

After creating an Excel table you can easily remove and add columns and rows using a variety of techniques. Read more information about techniques in Excel by clicking here

The resize command

The resize command is used in Excel to add and remove rows and columns on the table.

1. Click anywhere inside the table to reveal Table Tools option.

2. Click on Design and then select Resize Table

How to Add Columns and Rows to a Table in Excel

3. Starting with upper-leftmost cell, select a range of cells that you want to include.

4. Press Ok after selecting the range of cells you want.

To know about making a pivot table in Excel, click at http://www.whiskydemalta.com/make-pivot-table-report-excel-2016/

Other ways of adding rows and columns

By starting to type
Add a column or row in a table by beginning to type in a cell to the right of the left column or below the last row, by pasting data into the cell, or by inserting columns or rows between existing columns or rows.

By pasting
To add one by pasting, begin by pasting your data in the cell located in the leftmost cell immediately below the last row. Paste your data to the right of the column located on the rightmost column of the table to add a new column.

Using Insert to add a new row
1. To insert a new row, pick any cell or row that is not the header row then right click on it. Pick any cell and right click on it to insert a column.

2. Point to the Insert menu and select Table Rows Above to add a new row or pick Table Column to the Left in to add a new column. *screenshot