Tag: Excel Tutorial

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.

Copy & Delete Worksheets in Microsoft Excel

Copy and Delete Worksheets in Excel


Copy a Worksheet: Excel enables you to make a copy of a worksheet, which is a useful technique if you require a new worksheet that is similar to an existing worksheet. Rather than re-creating the new worksheet from scratch, you can make a copy of an existing worksheet and then edit the copy as needed.

Although you will most often copy a worksheet within the same workbook, it is also possible to copy the worksheet to another workbook.

Delete a Worksheet: If you have a worksheet that you no longer need, you can delete it from the workbook. This reduces the size of the workbook, reduces clutter in the worksheet tabs, and makes the workbook easier to navigate. It is important to note that you cannot undo a worksheet deletion.

Therefore, it is always a good practice to check the worksheet contents carefully before proceeding with the deletion. If the worksheet contains any data you need but you still want to remove the worksheet, cut or copy the data and paste it into another worksheet.

Operations with Ranges in Microsoft Excel

Operations with Ranges in Microsoft Excel


In Excel, a range is a collection of two or more cells that you work with as a group rather than separately. This enables you to fill the range with values, move or copy the range, sort the range data, filter the range to show only certain values, insert and delete ranges, hide entire rows or columns, and merge two or more cells.

You learn these and other range techniques in this chapter, and in later chapters you learn techniques such as formatting a range, applying a formula to a range, and building a chart from a range.

Select a Range: To work with a range in Excel, you must select the cells that you want to include in the range. After you select the range, you can fill it with data, move or copy it to another part of the worksheet, format the cells, delete the data, and so on. You can select a range as a rectangular group of cells, as a collection of individual cells, or as an entire row or column.

Fill a Range with the Same Data: If you need to fill a range with the same data, you can save time by getting Excel to fill the range for you. The most common method for filling a range in this way is to use Excel’s AutoFill feature, which makes it easy to fill a vertical or horizontal range with the same value. However, Excel also offers an alternative method that enables you to fill any selected range. See “Select a Range,” earlier in this chapter, to learn how to select a range of cells.

Fill a Range with a Series of Values: If you need to fill a range with a series of values, you can save time by using Excel’s AutoFill feature to create the series for you. AutoFill can fill a series of numeric values such as 5, 10, 15, 20, and so on; a series of date values such as January 1, 2011, January 2, 2011, and so on; or a series of alphanumeric values such as Chapter 1, Chapter 2, Chapter 3, and so on. You can also create your own series with a custom step value, which determines the numeric.

Move or Copy a Range: You can restructure or reorganise a worksheet by moving an existing range to a different part of the worksheet. For example, if you have two related ranges that are far apart on the worksheet, you can move one of them so that the ranges appear close to each other. You can also make a copy of a range, which is a useful technique if you require either a duplicate of the range elsewhere, or if you require a range that is similar to an existing range.

Change View of the Excel Window

Change View of the Excel Window



You can adjust the Excel window to suit what you are currently working on, by changing the view to match your current task. Excel offers three different views. Page Layout, which displays worksheets as they would appear if you printed them out. Normal, which is useful for building and editing worksheets. And Full Screen view, that is useful when you want to see the maximum amount of a worksheet on the screen.

Delete Data in a Range

How to Delete Data in a Range in Excel



Delete Data in a Range: If your worksheet has a range 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. Note that the technique in this section, only applies to deleting the data that exists within each cell in a selected range. It does not apply to deleting the actual range.

Delete a Range: If your worksheet contains a range that you no longer need, you can delete that range. Note that when you delete a range, Excel deletes not just the data within the range, but the range cells themselves. Excel shifts the remaining worksheet data to replace the deleted range. Note that the technique in this section deletes the actual cells from the selected range.

Customizing the Excel Chart Elements Outline

Customizing the Excel Chart Elements Outline



You can make a chart element stand out by customizing the element’s outline, which refers to the border that appears around the element, as well as to single-line elements, such as gridlines and axes. You can customize the outline’s color, its weight, that is, its thickness, and whether the line is solid or consists of a series of dots or dashes.