Tag: Excel Tutorial

Knowing the Excel Smart Tags

Knowing the Excel Smart Tags

 

 

 

 
You can make your Excel work faster and easier by taking advantage of smart tags. A smart tag is a special icon that appears when you perform certain Excel tasks, such as pasting data and using the AutoFill feature. Clicking the smart tag displays a list of options that enable you to control or modify the task you just performed.

Some smart tags appear automatically in response to certain conditions. For example, if Excel detects an inconsistent formula, it displays a smart tag to let you know.

 

 

How to Apply a Style to a Chart Element in Excel

How to Apply a Style to a Chart Element in Excel

 

 

 

 
You can reduce the time it takes to format a chart element by applying a style to that element. Excel comes with more than 40 predefined element styles, each of which is a collection of chart formatting features.

Each style includes one or more of the following formatting features: a background, which is usually either a solid color or a color gradient; an outline, which is usually a solid line with a color that matches or complements the background; and one or more special effects, such as a shadow or bevel.

 

 

Formatting Numbers in Excel

Learn to Format Numbers in Excel

 

 

 

Apply a Number Format: You can make your worksheet easier to read by applying a number format to your data. For example, if your worksheet includes monetary data, you can apply the Currency format to display each value with a dollar sign and two decimal places.

Excel offers ten number formats, most of which apply to numeric data. However, you can also apply the Date format to date data, the Time format to time data, and the Text format to text data.

Change the Number of Decimal Places Displayed: You can make your numeric values easier to read and interpret by adjusting the number of decimal places that Excel displays. For example, you might want to ensure that all dollar and cent values show two decimal places, while dollar-only values show no decimal places. Similarly, Excel often displays values with a large number of decimal places.

If you do not require the extra decimals. For example, if the values are simple temperatures or interest rates. You can make them easier to read by reducing the number of decimals. You can either decrease or increase the number of decimal places that Excel displays.

 

 

Apply a Workbook Theme in Excel

How to Apply a Workbook Theme in Excel

 

 

 

 

You can give your workbook a completely new look by selecting a different workbook theme. Each theme consists of the workbook’s colors, fonts, and effects. Excel offers more than 20 predefined workbook themes. To get the most out of Excel’s workbook themes, you must apply styles to your ranges, and to your charts.

 

 

How to Assign a Name to a Range in Excel

 

 

 

 
You can make it easier to navigate Excel worksheets and build Excel formulas by applying names to your ranges. A range name is a text label that you apply to a single cell or to a range of cells. Once you have defined a name for a range, you can use that name in place of the range coordinates, which has several benefits.

For example, range names are more intuitive than range coordinates, particularly in formulas; range names are more accurate than range coordinates; range names are easier to remember than range coordinates; and range names make it easier to navigate a worksheet.

Define a Range Name: Before you can use a range name in your formulas or to navigate a worksheet, you must first define the range name. You can define as many names as you need, and you can even define multiple names for the same range. You can create range names by hand, or you can get Excel to create the names for you automatically based on the existing text labels in a worksheet.

Use Worksheet Text to Define a Range Name: If you have several ranges to name, you can speed up the process by getting Excel to create the names for you automatically based on the range’s text labels. Text labels make a worksheet easier to read and understand, but in this section you see that they also make it easier to define range names.

For example, if you have a column of sales data that has the label “Sales” on top, Excel can automatically apply the name “Sales” to that range. You can create range names from worksheet text when the labels are in the top, or bottom, row of the range, or the left, or right, column of the range. Select the range or ranges you want to name.

Navigate a Workbook Using Range Names: One of the big advantages of defining range names is that they make it easier to navigate a worksheet. You can choose a range name from a list and Excel automatically selects the associated range.

This is much faster than scrolling through a workbook by hand, or by entering a cell or range reference into Excel’s Go To command. Excel offers two methods for navigating a workbook using range names: the Name box and the Go To command.

Delete a Range Name: If you have a range name that you no longer need, you can use Excel’s Name Manager to delete it. This is a good practice, because you do not want to have unused range names in a workbook. Getting rid of unneeded range names reduces clutter in the Name Manager dialog box, and it also makes the Name box easier to navigate.

It is also a good idea to delete unused range names because you may need to reuse the names for different ranges later on.

 

 

ACCRINT Function in Excel

Using the ACCRINT Function in Excel

 

 
Now we are going to learn all about formulas and functions. This is the best way to learn them:
 

 

ACCRINT

Returns the accrued interest for a security that pays periodic interest.

 

ACCRINT(issue; first_interest; settlement; rate; par; frequency; [basis]; [calc_method])

 

The ACCRINT function syntax has the following arguments:

  • Issue Required. The security’s issue date.
  • First_interest Required. The security’s first interest date.
  • Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Rate Required. The security’s annual coupon rate.
  • Par Required. The security’s par value. If you omit par; ACCRINT uses $1;000.
  • Frequency Required. The number of coupon payments per year. For annual payments; frequency = 1; for semiannual; frequency = 2; for quarterly; frequency = 4.
  • Basis Optional. The type of day count basis to use.
    BASIS DAY COUNT BASIS
    0 or omitted US (NASD) 30/360
    1 Actual/actual
    2 Actual/360
    3 Actual/365
    4 European 30/360
  • Calc_method Optional. A logical value that specifies the way to calculate the total accrued interest when the date of settlement is later than the date of first_interest. A value of TRUE (1) returns the total accrued interest from issue to settlement. A value of FALSE (0) returns the accrued interest from first_interest to settlement. If you do not enter the argument; it defaults to TRUE.

 

Remarks

 

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
  • Issue; first_interest; settlement; frequency; and basis are truncated to integers.
  • If issue; first_interest; or settlement is not a valid date; ACCRINT returns the #VALUE! error value.
  • If rate = 0 or if par = 0; ACCRINT returns the #NUM! error value.
  • If frequency is any number other than 1; 2; or 4; ACCRINT returns the #NUM! error value.
  • If basis < 0 or if basis > 4; ACCRINT returns the #NUM! error value.
  • If issue = settlement; ACCRINT returns the #NUM! error value.
  • ACCRINT is calculated as follows:

    • Ai = number of accrued days for the ith quasi-coupon period within odd period.
    • NC = number of quasi-coupon periods that fit in odd period. If this number contains a fraction; raise it to the next whole number.
    • NLi = normal length in days of the ith quasi-coupon period within odd period.

 

Interactive Example

Change the data in the non-colored cells and see how the result changes in the yellow cells.

 

 

Quickly select or filter by strikethrough formatted text

Sort by strikethrough formattingSave 5 minutes a day by using ASAP Utilities to quickly select or filter by strikethrough formatted cells. “How can I filter for items that have a ‘Strikethrough’?” Strikethrough formatting (my struck through text) is often used to mark tasks as being completed or items to be ready for removal. The quickest way to apply […]