Tag: Excel

Convert Time to Seconds in Microsoft Excel

Convert Time to Seconds in Microsoft Excel

 

Do you ever have had to show a data of time in seconds? In Excel Doing it is really simple and will take only a few seconds.

We are used to express the values of time in hours, minutes and seconds. But probably sometimes you need these values only time expressed in seconds. Suppose the following example.

Convert To Seconds Time Data Initial Input

In this example we have a data equivalent to 1 hour, 35 minutes and 15 seconds. To find quickly the number of seconds is sufficient to modify the format of the cell. Right click on the cell and select Format Cells option and should go to the Custom section.

Format Custom Cells Seconds

In the Type text box you should specify the custom format [ss] and click OK

Convert To Seconds Time Data Result

Excel will show the total of equivalent seconds of the time data in the cell.

 

Concatenate Functions with Microsoft Excel

Concatenate Functions in Microsoft Excel

 

There are some functions in Excel where any arguments allow to specify a condition so that if it is met will perform the action. Such is the case of COUNTIF function or SUMIF function.

Such functions are useful to apply an action a cause of certain condition. For example, if I want to count all the people in the range B1: B20 that are older than 30 years can apply the following formula: =COUNTIF(B1:B20, ">30")

Concatenate Functions with Excel Initial Data

But now, what if I want to count only those who are aged above average? One option would be to get the average in a separate cell and then make the comparison with the obtained value to determine whether or not you can count every item. Rather than make a separate calculation, we can make use a special quality of the COUNTIF function argument condition.

This quality is due to the argument condition is a text string and therefore can make a chain with the result of another function. Observe carefully how to perform this concatenation in the second argument of the function: =COUNTIF(B2:B21, ">"&AVERAGE(B2:B21)) Concatenation in Excel is done with “&” and for this example I’m using the AVERAGE function so that Excel gets the average of the range and then the concatenation to get ready the argument condition.

After making the evaluation of this expression Excel shows the correct result.

Concatenate Functions with Excel COUNTIF and AVERAGE

For this example I used the AVERAGE function, but you can use any function that helps form the argument condition you need. (Source: Excel Total)

 

Waterfall Charts in Excel

Create a Waterfall Chart in Excel

 

 
A waterfall chart helps to visualize the contribution made by each of the parties to the total. This chart type is distinguished by having “floating” columns because they give the impression of being suspended in the air.
 

This is the waterfall chart that we are going to do:

Waterfall Chart with Microsoft Excel

In our example we are showing the sales of a company in 4 countries where they have presence: Brazil, Colombia, Argentina and Mexico. Each country brings a portion of the total sales of the company. TO begin with the construction of the chart, we have to take in count 2 elements: – Base Values: These values are the blank spaces on which “will float” the columns. – Connectors: A series of data will be needed to show the connectors between the floating columns. Now we are going to see how the Data Table is.

Waterfall Chart Data Table

Create the Waterfall Chart To create a waterfall chart select the data range to be graphed (A2: G6) and go to the Insert tab in the Charts Section and click the button to select Column and the Stacked Column option. As a result you have the following chart in Excel.

Initial Waterfall Chart

Now we swap the rows and columns. Right click on the graph and select the Select Data option, in the dialog box click on the Switch Row/Column button and accepts the changes. The graph will look as follows:

Switched Rows and Columns in a Waterfall Chart

The upper columns are the corresponding connectors and now will become lines. To make the change, you need to right click on the first of the series of connectors (in this example the green columns). In the pop-up menu you must select Change Series Chart Type and go to the Line section and choose the Lines chart type. Repeat this process for all columns of connectors and you finally you will have the chart below.

Change Chart Type for the Connectors in a Waterfall Chart

To format each connector you need to right click on each line and select the Format Data Series and you should consider making the following changes: In the Line Color section select Solid Line option and change the color to black In the Line Style section sets a width of 0.25 pt. and in Dash type choose Square Dot. Apply these changes for each connector. Optionally remove the gridlines of the chart and have the following result.

Change Connectors Format in a Waterfall Chart

It only remains to remove the fill color of the base values, for do this right-click on the corresponding data set (in this example the blue columns) and select the menu option Format Data Series and select in the Fill section No Fill option. When you close the dialog box will get the following result.

Format Base Columns in a Waterfall Chart

You need only to remove the legend to having a waterfall chart as shown the beginning of the article. (Source: Excel Total)
 

 

Goal Seek in Microsoft Excel

Learn About Goal Seek in Microsoft Excel

 

 

Excel includes several tools for data analysis and Goal Seek is a technique used to easily find the number that fulfills the necessary conditions to achieve a goal.
 

This tool will help you do many tests of values in a formula to find the exact value that meets the conditions. That is, if we know the result of a formula, but we are not sure of the value of one of its arguments, then Goal Seek will help us to test various scenarios to find the exact value needed. For example, suppose we have the following problem.
 
Goal Seek Initial Data
 
I have a number multiplied by another number X and this should give me the result shown. Although this is an easy problem to solve mathematically will be useful to show the functionality of Goal Seek. I begin by defining an important formula. In cell C3 will place the formula to be met to obtain the desired result, ie, = B1 * B2 it should result in 689
 
Goal Seek Formula
 
For now the result in C3 is zero, because there is no value in cell B2, however, when you go looking for the appropriate value for B2, the value of cell C3 will become the same as B3 when the correct value is found.
 
Goal Seek Command
 
Now you need to click the Data tab and select If Analysis where an options menu will be displayed and you must choose Goal Seek.
 
Goal Seek Command Button
 
Immediately the dialog box Goal Seek is displayed, where you must fill in the following text boxes.
 
Goal Seek Dialog Box
 
– Set cell: It is the cell that contains the formula – To Value: This is the number we want as a result of the formula. – By changing cell. The cell to be modified to have the desired result. Once the parameters have been set, you must click on the OK button and Excel will begin to enhance the necessary calculations. When have found a suitable result the Goal Seek Status dialog box is displayed.
 
Goal Seek Status Dialog Box
 
This dialog box just notified that it has reached the target by changing the specified cell. When you close the dialog box you can see the result. For our example Excel was doing several tests until the result of 146,60 is the number multiplied by 4,7 will give the expected result.
 
Goal Seek Results
 
(Source: Excel Total)

 

Insert a Row & a Column in Microsoft Excel Worksheets

Excel Online Tutorial – Insert a Row & a Column in a Worksheet

You can insert a row or column into your existing worksheet data to accommodate more information. This is particularly useful if the information you need to add fits naturally within the existing data, rather than at the end.

When you insert a row, Excel shifts the existing rows down, so you must first determine the row above where you want your new row to appear. Similarly, when you insert a column, Excel shifts the existing columns to the right, so you must first determine the column to the left of where you want your new column to appear.

Extract Information from a Date in Microsoft Excel

Learn to Extract Information from a Date in Microsoft Excel

 

 

Using dates in Excel is very common and probably more than once you need to extract information from a specific date as the day, the month or the year. TEXT function offers an alternative to obtain such information.

 

 

Show the month of a date
 
Suppose the next column of date, the first task will be to obtain the month of each and present it as a text string. The function used in column B is: =TEXT(A2, "m")

 
Extract Month Information from a Date – Single Number

 
Indeed I get the month number each date. Now see what happens if I use the same function but in the second argument put two letters “m” as follows: =TEXT(A2, "mm")

 
Extract Month Information from a Date – Two Numbers

 
The difference from the previous result is that now each month are displayed with two digits putting a leading zero if required. In the next picture you can see what happens if in the second parameter of the function TEXT put three, four and five letters “m”.

 
Extract Month Information from a Date – Other Formats

 
Show the day of a date
 
Similarly as in the previous example we can obtain information of the day. Notice how using the TEXT function and specifying the second parameter as the letter “d” we may obtain information about the day.

 
Extract Day Information from a Date

 
TEXT function helps us to specifically identify the name of the day and thus can make an analysis of information according to specific days of the week.
 
Show the year of a date
 
Finally, for the year there are only two possible alternatives that are getting the year with two digits or four-digit format.

 
Extract Year Information from a Date
 
Working with dates is very important and aware of the possibility offered by the TEXT function to work with this type of data will be very useful. (Source: Excel Total)
 

 

Using the Gridlines in an Excel Worksheet

How to use the Gridlines in Excel Worksheets

You can make your worksheet look cleaner and make the worksheet text easier to read by turning off the sheet gridlines. When you do this, Excel displays the worksheet with a plain white background, which often makes the worksheet easier to read.

If you find you have trouble selecting ranges with the gridlines turned off, you can easily turn them back on again.

Create or Move Excel Worksheets

Create and Move Excel Worksheets

Create a New Worksheet: Excel supports multiple worksheets in a single workbook, so you can add as many worksheets as you need for your project or model. Each new Excel workbook comes with three worksheets, but it is not uncommon to require four or more worksheets in a workbook.

In most cases, you will add a blank worksheet, but Excel also comes with several predefined worksheet templates that you can use. Note that there is no practical limit to the number of worksheets you can add to a workbook.

Move a Worksheet: You can organize an Excel workbook and make it easier to navigate by moving your worksheets to different positions within the workbook. Although you will most often move a worksheet to a different position within the same workbook, it is also possible to move a worksheet to another workbook.

Change Fonts Schemes in Microsoft Excel

Font Schemes in Microsoft Excel

You can add visual appeal to your workbook by selecting a different font scheme. Each font scheme has two defined fonts: a heading font for the titles and headings, and a body font for the regular worksheet text.

Excel offers more than 20 font schemes. To get the most out of Excel’s font schemes, particularly the heading fonts, you must apply styles to your ranges.

Chart Layout & styles

Excel Chart Layout and Styles




You can quickly format your chart by applying a different chart layout and a different chart style. The chart layout includes elements such as the titles, data labels, legend, gridlines, and data table.

Excel’s Quick Layouts feature enables you to apply these elements in different combinations with just a few mouse clicks. The chart style represents the colors used by the chart data markers and background.