Tag: Excel

Using the Menu System: Microsoft Excel

Using the Menu System in Microsoft Excel

The excel menu system is a basic knowledge that you have to learn initially. In the ribbon, you can find almost every command to use in Microsoft Excel; you can find all that commands organised in tabs, according to each command use.

The Excel Ribbon in Microsoft Excel
You can find button about formatting, inserting objects, about formulas, about data, view and review, and the file management options.

The Microsoft Excel File Tab Menu

Put the mouse cursor in each button without clicking and you can find an explanation of the button, try to explore all this commands and you can learn what are all the powerful tools that Microsoft Excel has. Learn more about the Microsoft Excel Ribbon in the following video:

What is Microsoft Excel Used For

What is Microsoft Excel Used For?

Microsoft Excel is one of the most used software by people in professional and personal level. It is a powerful tool for productivity that allows you to keep you data organised.

The most common uses are: 1. You can make simple spreadsheets and tables to keep tracking you business indicators …

Simple Spreadsheet for Business Tracking
2. Make big tables with complex budgets of incomes and expenses in business or in your home …

Complex Business Budget Excel Spreadsheet
3. Manage big bunch of data in organized databases …

Manage Databases in Microsoft Excel
4. Create and manage lists, of clients, products, employees, etc …

Manage lists in Microsoft Excel
5. Create one of the different types of Charts to analyze data in a simple way …

Create Microsoft Excel Charts

6. Make complex calculation with one of the more than 300 functions that Excel has …

Use Microsoft Excel Functions
7. Show graphs and visual objects with the Excel Smart Tags …

Use Excel Smart Tags
8. Analyze your data and show tables with filtered data with the Pivot Tables …

Create Microsoft Excel Pivot Tables
These are some of million of uses for Microsoft Excel, Learn more with Microsoft Excel MOOC

Create a Marimekko Chart in Excel

How to Create a Marimekko Chart in Excel

Marimekko charts encode two variables: one along the height of the vertical axis and another using the width of the bars or columns. This one from The Economist, for example, has GDP per capita ($) on the vertical axis and share of global population (%) along the horizontal axis.

There are alternatives, of course; they could have used a scatterplot or maybe two column charts. But the Marimekko, I think, does a nice job extending the standard column chart in another dimension.


How to Create a Marimekko Chart in Excel


Learn How to construct it in the following link: Marimekko Chart in Excel

Create a Basic Chart in Microsoft Excel

Create a Basic Chart in Microsoft Excel

When you have organized data in a table is very useful to show it in a better way than the simple data table.

You can create a Microsoft Excel Chart and show the data in a graphical way. In this video you can learn step by step how to do it.

In this example you can see a kind of Chart in Excel, but you can create a lot of different types of Chart to show your informacion

Using the Excel Counting Functions

Using the Excel Counting Functions

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

The COUNTA function counts the number of cells that are not empty in a range.

The COUNTBLANK function counts empty cells in a specified range of cells.

How to Create Target Lines in Excel Charts

How to Create Target Lines in Excel Charts

In this video, you can Learn how to add a target line in bar chart. You can do that for make an evaluation of the results of a variable compared with a target.

The way to do that is adding the column of the target values to the chart. Then you have to select the new target serie from the chart. Finally change the chart type, from bars to line.

Rounding in Microsoft Excel

How to Rounding Data in Microsoft Excel

 

The ROUND, ROUNDUP and ROUNDDOWN functions are useful when rounding numbers in Excel. These functions are categorize Math and trigonometry.

The ROUND function in Excel

We use the ROUND function to work with any number of decimal places. Unlike when we apply a format on the cell to display more or fewer decimal, the ROUND function effectively modifies the stored value in the Excel cell. The syntax for the ROUND function is: ROUND(number, num_digits)

The function only requires two arguments, the first of them the number we want to round, the second is the number of decimal places that we need. Consider the following examples: = ROUND (3.1416, 0). It returns the value 3, because we are indicating zero as the number of decimals we need. = ROUND (3.1416, 3). It returns the value 3,142 because we specified three decimal places.

ROUNDUP and ROUNDDOWN functions in Excel

These functions have the same arguments that the ROUND function and the difference is that ROUNDUP always rounds up the specified number and the ROUNDDOWN function makes rounding down.

In the next picture you can see the difference between each of the three functions by applying rounding the value of the mathematical constant PI.

Rounding Data Options in Excel

Each function applies a different rounding even if you applied the same number of decimal places. Notice how the result of the function ROUND sometimes agrees with the result and sometimes ROUNDUP agrees with the result ROUNDDOWN. The next time you need round in Excel know that there are three functions that can help you get the desired result. (Source: Excel Total)

 

How to Edit & Delete Data in Excel

How to Edit & Delete Data in Excel

 

 

 

 
Edit Cell Data: Once you enter text, a number, a date, or a time into a cell, that cell data is not set in stone.If the data you entered into a cell has changed or is incorrect, you can edit the data to the updated or correct value. You can edit cell data either directly in the cell or by using the Formula bar.

Delete Data from a Cell: If your worksheet has a cell that contains data you no longer need, you can delete that data. This helps to reduce worksheet clutter and makes your worksheet easier to read.

 

 

Two Variable Data Table in Excel

Two Variables Data Table in Excel

 

 

In a previous article I showed the benefits of the data tables (Data Table in Microsoft Excel) as an option of What-If analysis. This time we review a classic example of the use of a table of data for analysis of two variables.

 

The scenario is as follows. Suppose I want to apply for a personal loan from a bank, I’m doing a research with several banks and each of them has given me an interest rate as well as several different payment terms. The first case that I investigated is:

Two Variables Data Table Initial Data

Two Variables Data Table Instead of making the calculation for each option interest rate and term, adequately I will accommodate the data to form a data table. In the rows I will specify different rates of interest and in the columns possible terms.

Rows and Columns for the Data Table

 

To create the data table with two variables I will select the range B4: G11 and I go to the Data tab and click the “What-If” button to select whether the “Data Table” option. Within the dialog I must choose each of the input cells for the data table as follows:

What-If Data Table Dialog Box

When click OK the data table is generated

Two Variable Data Table Results

In this data table can observe what we expected, the higher is the term the monthly payments are reduced and the greater is the interest rate your monthly payment increases. With this information I can properly analyze and choose the option that best fits my chances of monthly payment. (Source: Excel Total)

 

 

How to Use Subtotals in Microsoft Excel

How to Use Subtotals in Microsoft Excel

 

When you have a data table with a lot of information, subtotals in Excel can help us understand and interpret the information. Excel lets you add subtotals in a very simple way.

Suppose the following data table where I want to subtotal sales for each month:

Subtotals in Excel Initial Data

The first thing to do is sort the data by the column on which the subtotals are going to be obtained. For this example I will order the data for the “Month” column. To do this you have to click any cell and then choose the Sort Command in the Data Tab. Finally click in Descendent command. (In Windows click in Sort from older to newer).

Sort a Table Button

The table is sorted

Data Sorted for Use Subtotals

The Subtotals Button in Excel

For insertion of the subtotals in Excel we must press the Subtotal command from the Data tab.
Excel shows the Subtotals dialog box

Subtotals Dialog Box in Excel

Now we are going to explain each box. The first one is At each Change in: we are going to select Month, that means that every change in the column month excel is going to put the subtotal. The second is the Function, in this example we are going to use Sum function. Then select the Sales item in the Add subtotal to menu, this means that excel is going to use the function with the date of the selected column. Finally click OK and excel calculate the subtotals:

Table with Subtotals Added in Microsoft Excel

Notice how Excel has inserted a new row containing the subtotal for each month. In addition to the left of the spreadsheet Excel places additional controls are useful to hide or display groups of data according to the subtotals. Clicking on any of them the corresponding group expands or contracts:

Subtotals Groups in Microsoft Excel