Browsing all articles tagged with MS Excel

How to Use the MS Excel Keyboard Shortcuts

Most probably, when it is your first time to work with Microsoft Excel, you will concentrate on just completing your task instead of learning how to do the task more effectively for a faster completion of your job. However, once you’re prepared to learn regarding the keyboard shortcuts that are used in Microsoft Excel, this article will provide you the most significant and beneficial Excel keyboard shortcuts that are presently available.

Using the Excel keyboard shortcuts offers you these two benefits:

Keeping your hands on the keyboard is more convenient. It is faster to move your hand from keyboard into the mouse to the keyboard.

Your muscles will not be stressed out too much with less movement of your hands.

This article has categorized the Excel shortcuts into connected areas. Through this way, it will be easier for you to learn a collection of keyboard shortcuts one by one, rather than learning all shortcuts at once.

Note: You have to remember that those keyboard shortcuts with a plus sign ( e.g. Ctrl + O ), means that you need to hold the Ctrl key, then press the “O”, while not releasing the Ctrl key.

Excel Keyboard Shortcuts: Files

This small collection of keyboard shortcuts is probably the most important and basic among the Excel shortcuts. These shortcuts can be used to open, close and save the Excel workbooks.

  • Ctrl + N: This shortcut will allow you create a new Excel workbook.
  • Ctrl + S: Its function is to save the Excel workbook.
  • Ctrl + O: It functions by opening an Excel workbook that exists.

Excel Keyboard Shortcuts: Editing

The following are the most common Excel keyboard shortcuts that you can use in editing the Excel workbook. The most preferred shortcut within this group is probably the Ctrl + Z.

  • Ctrl + Z: This shortcut will allow you to undo the last change that you have made in your workbook. It can be used repeatedly to remove the changes many times.
  • Ctrl + Y: Its function is to redo the last undo. However, it can only be used if you’ve just utilized the Undo command.
  • Ctrl + C: It allows you to copy a selected item of range of items in your Workbook. Then after copying, you can paste it by using the paste keyboard shortcut.
  • Ctrl + V: Its function is paste the item or range of items that you have selected in your workbook.
  • Ctrl + X: It cuts your selected item or range of items in your workbook then you can place it in any region of your workbook by using the paste keyboard shortcut. Cut and copy are different from each other. Cut functions by deleting your selected item or range of items, while copy does not.
  • Delete: It deletes your selected item or range of items. You can utilize this keyboard shortcut when you want to delete more than one cell at once.
  • Backspace: This can be used if you want to edit an item in a cell. However, it can only be used in deleting an item, not a range of items.

Excel Keyboard Shortcuts: Menu

These keyboard shortcuts enable you to activate the different menus on menu bar even without using the mouse. But you have to note that the combination key of these keyboard shortcuts is the Alt key.

  • Alt + H: Provides access to Help menu.
  • Alt + W: Provides access to Window menu.
  • Alt + D: Provides access to Data menu.
  • Alt + T: Provides access to Tools menu.
  • Alt + I: Provides access to Insert menu.
  • Alt + V: Provides access to View menu.
  • Alt + E: Provides access to Edit menu.
  • Alt + F: Provides access to File menu.

After utilizing these keyboard shortcuts, you could utilize arrow keys to select from the menu options and press “Enter” to choose an option.

Excel Keyboard Shortcuts: Selecting

This collection of shortcuts is very helpful for a quick selection of items within the Excel workbook in different ways.

Ctrl + Spacebar: This shortcut allows you to select the existing column.

Shift + Spacebar: Its function is to select the existing row.

Ctrl + A: It is known as the “Select All” keyboard shortcut. It allows you to select all the items in your worksheet.

These are just some of the keyboard shortcuts available in Microsoft Excel. You can go for further reading to learn more about them.

 

How to Locate Duplicate Records Quickly By Using Conditional Formatting in MS Excel

Microsoft Excel is one of the most beneficial computer programs that are widely used throughout the whole world. This software is employed for making spreadsheets. Maybe, some of you may think that Excel is as simple as its definition, but it’s not actually as simple as that. This computer program has the capability to enhance efficiency, not time-consuming and the best thing is that, it has the ability to perform complex computer operations. Microsoft Excel is designed for a well organized data. In addition, MS Excel contains numerous functions and one of which allows the user to format the worksheet to find and remove errors that can cause massive problems on the spreadsheet. This specific function of MS Excel is most beneficial to those who are working with databases containing large sets of data. It helps the user for a quicker scanning of errors so that they can be easily edited or removed from the database. Microsoft Excel can be a very useful program that is utilized to accomplish complicated tasks, whether you use it at work or even at home.

Spreadsheet software like Microsoft Excel is usually utilized as databases for mailing lists, sales records, and parts inventories. Normally, databases are arranged into rows of values called records. In a record, the values in every cell or field in the row is connected like phone number, address and company’s name.

However, you would find it difficult when you are already working with huge databases. One of the common consequences that take place in a large database is that of repetition or duplication of records. This duplication can take place if:

  • Several records contain one or more fields like the address or name field, which contain similar data.
  • The whole record is entered into the database several times which can result in two or more similar value.

Duplication of records can cause big problems in your database so it is a very good idea to locate and eliminate those duplicate data regularly.

When you are using Microsoft Excel 2007, it is not difficult to locate duplicate values like data records, dates, numbers and formulas using conditional formatting. This article will provide you an example and the steps of using conditional formatting to locate duplicate data in Microsoft Excel 2007. But before using conditional formatting to locate duplicate values in your spreadsheet, here is a brief overview of conditional formatting.

Overview of Conditional Formatting

Conditional formatting instructs Microsoft Excel to format all the cells that meet a specific condition in a certain way. For example, you can employ conditional formatting to alter the background color of cells having duplicate data. Through this, it will be easy for you to locate duplicate values in your spreadsheet. You can also use it to alter the font color of all cells having negative values.

Of course, there is a manual method to alter the formats of cells to meet a certain condition. However, if you do it manually, it would be time-consuming since formatting cells is a repetitive procedure. Especially if you are working with a huge set of data or your data change often. That’s the reason why there is conditional formatting, which will let you to formal cells automatically to meet a certain condition.

Locate Duplicate Data by Using Conditional Formatting

  • As an example, you can input the following values into cells B1 to B6: 10, 11, 15, 11, 14, 11
  • The drag cells B1 to B6 on the worksheet.
  • Click Home on the menu bar.
  • Choose and click over the Conditional Formatting symbol that is on the ribbon and then a drop down menu will open.
  • Then choose and click over the Highlight Cell Rules, then Duplicates Values. After that, the dialog box related to conditional formatting will open.
  • From the list in the dialog box, select “Light Red Fill with Dark Red Text”.
  •  Click OK to activate you selections. Then close it.
  • On your spreadsheet, you will see that cells B2, B4 and B6 are formatted having a light red background color and the text are colored with dark red since the three cells enclose duplicate data, which is the number 11.

The same procedure can be utilized appropriately to make use of conditional formatting to trace duplicate records in an Excel database, as well as duplicate dates, formulas or even other values contained in your available data range.

 

ACCRINT Excel Formula

Microsoft Excel has lots of built-in functions that are very useful in all businesses and in almost all fields. This spreadsheet program features functions that can be used in businesses especially when it comes to financial and accounting matters. The financial functions in Excel are the so-called ACCRINT function. Let’s begin by learning about this function by knowing its description and working with some examples that can enable you to learn about the usage of the ACCRINT function.

Description of Excel’s ACCRINT Function

The ACCRINT function in Microsoft Excel is useful in calculating the added interest for a security that pays periodic interest. The formula of the ACCRINT function is:

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

The Arguments in this Formula are as follows:

  • Issue – The date of issuance of the security.
  • First_interest – The first interest date of the security
  • Settlement – The date of settlement of the security
  • Rate – The yearly coupon rate of the security
  • [par] – The par value of the security. If this argument is omitted, par uses the default value which is $1000.
  • [basis] – This is an optional argument. It specifies the basis of day count that will be used in the computation. The following are the possible values of this argument and their meanings:
  • (or omitted)  -  US (NASD) 30/360
  •   actual/actual
  •  actual/360
  •  actual/365
  •   European 30/360

Note: the settlement, first interest and issue dates can be supplied as either of the following

  • Dates that are returned from other functions or formulas
  • Cell reference which contains the dates

If these dates are supplied as text, there is a possibility that Excel may not understand this because of various settings of date interpretation and date systems.

Even though these dates can also be supplied as serial numbers, this is not advisable due to the fact that serial numbers may differ across various computer systems.

For a better understanding about the usage of the ACCRINT function, here’s an example which you can try in your own Excel spreadsheet.

For instance, the date of settlement is 01-Apr-10, the date of first interest is 30-Jun-10 and the date of issuance of the security is 01-Jan-10. The yearly coupon rte is 8%. The par value of the security is $10,000, payments are made quarterly, and the day count basis that is used is a US (NASD) 30/360.

If you will use the ACCRINT function formula and input all the values that were given above, the function will return the result which is $202.222. You can try this example in your own blank spreadsheet for practical applications.

Easy Steps on How to Use the ACCRINT Function

  • If Excel returns the #NAME? value, you should enable first the Analysis Toolpak. From the Tools menu, select add-ins. Click on the checkbox adjacent to Analysis Toolpak and then click the OK button.
  • Learn the formula of the ACCRINT function that was given above.
  • Supply the arguments in the formula. It is better to use the dates that are returned from other functions or formulas to avoid the problems that may take place when supplying a date as text.
  • Indicate the basis argument if required. If the basis argument is omitted, it takes the default value of 0 which is equivalent to US (NASD) 30/360.
  • Finally, you can try the example that was given earlier so that you can learn more about the usage of the ACCRINT function.