Excel Tips

Troubleshooting Excel Files with unjustified size or opening/loading time

CA, CS, CMA - We all have came across than one excel file which does not contain that much information but still taking up a space of several MBs and makes our PC standstill while opening or editing it. You might have wondered what is the extra data that is stored in that file. Here are few tips that […]...

Read More

95 Timesaver Excel basic & advanced excel shortcuts

CA, CS, CMA - Pre requisites to help understand the file: 1. What is Timesaver Excel Shortcuts? 95 selected basic & advanced excel shortcuts which can simplify work 2. Is there any thing one should know in advance before reading the shortcuts? Yes. Below are the two points which you should know in advance before going through the shortcuts: [&helli...

Read More

Use Flash Fill In Excel and Feel Fresh

CA, CS, CMA - Microsoft Excel is undoubtedly most user friendly tool and being extensively used all over the world. We might be using it daily or maybe casually, but probably always wished that we knew more about this dynamic program. Most of us don’t use even 5% of its capabilities. While most of us know use of VLOOKUP, […]...

Read More

Some Useful Excel Functions for Chartered Accountants

CA, CS, CMA - Working as a Chartered Accountant or an Article in any company or firm, it is always expected from us that we are perfect in numbers as far as financial numbers are concerned. The one tool which is very common and useful in every industry and in every country, is MS-Excel. So, in this article we […]...

Read More

Excel With Excel! Chapter 2: GOAL SEEK

CA, CS, CMA - Another week has passed by and it’s time to get the hang of another feature in Excel. ‘It takes time to save time’ and When we invest time to learn Excel, we are actually saving our time. ♦ Background: You need to borrow some money – – You know how much you want (the amount […]...

Read More

Best Strategies to Excel in your Microsoft Exam 70-741 and Benefits of Utilizing Practice Tests

CA, CS, CMA - Test anxiety can affect not just the result of your certification exam but also your wellbeing. It may lead to exam failure, or worst, mental breakdown! And if you don’t want to pull a stressful all-nighter, there’s no better way than to have a game plan. For those who plan to take Microsoft exam 70-741, […]...

Read More
Sorry No Post Found
Sorry No Post Found

Recent Posts in "Excel Tips"

Troubleshooting Excel Files with unjustified size or opening/loading time

We all have came across than one excel file which does not contain that much information but still taking up a space of several MBs and makes our PC standstill while opening or editing it. You might have wondered what is the extra data that is stored in that file. Here are few tips that […]...

Read More
Posted Under: CA, CS, CMA |

95 Timesaver Excel basic & advanced excel shortcuts

Pre requisites to help understand the file: 1. What is Timesaver Excel Shortcuts? 95 selected basic & advanced excel shortcuts which can simplify work 2. Is there any thing one should know in advance before reading the shortcuts? Yes. Below are the two points which you should know in advance before going through the shortcuts: [&helli...

Read More
Posted Under: CA, CS, CMA |

Use Flash Fill In Excel and Feel Fresh

Microsoft Excel is undoubtedly most user friendly tool and being extensively used all over the world. We might be using it daily or maybe casually, but probably always wished that we knew more about this dynamic program. Most of us don’t use even 5% of its capabilities. While most of us know use of VLOOKUP, […]...

Read More
Posted Under: CA, CS, CMA |

Some Useful Excel Functions for Chartered Accountants

Working as a Chartered Accountant or an Article in any company or firm, it is always expected from us that we are perfect in numbers as far as financial numbers are concerned. The one tool which is very common and useful in every industry and in every country, is MS-Excel. So, in this article we […]...

Read More
Posted Under: CA, CS, CMA |

Best Strategies to Excel in your Microsoft Exam 70-741 and Benefits of Utilizing Practice Tests

Test anxiety can affect not just the result of your certification exam but also your wellbeing. It may lead to exam failure, or worst, mental breakdown! And if you don’t want to pull a stressful all-nighter, there’s no better way than to have a game plan. For those who plan to take Microsoft exam 70-741, […]...

Read More
Posted Under: CA, CS, CMA |

Excel With Excel! Chapter 2: GOAL SEEK

Another week has passed by and it’s time to get the hang of another feature in Excel. ‘It takes time to save time’ and When we invest time to learn Excel, we are actually saving our time. ♦ Background: You need to borrow some money – – You know how much you want (the amount […]...

Read More
Posted Under: CA, CS, CMA |

Excel with Excel – Explaining Excel from a CA viewpoint

Excel's ‘Consolidate’ feature helps in consolidating data in worksheets (located in one workbook or multiple workbooks) into one worksheet. Even though this is predominantly used at the managerial level (say, for aggregating various financial figures from multiple branches), it can come handy to the Chartered Accountants as well....

Read More
Posted Under: CA, CS, CMA |

Use of SUMIF function in excel

Writing an article to appraise readers, how they can use SUMIF in analyzing the financial data. Use of SUMIF in excel: ♠ SUMIF is used to sum up the values of a given range as per the specified criteria. ♠ For example: You are working on GST liability computation and analyzing your ITC register to […]...

Read More
Posted Under: CA, CS, CMA |

A Very Useful Online Tool for Budgeting and Tax Returns

Managing your personal finances and tax returns can be quite tedious, but many people have turned to themselves in order to avoid spending unnecessary funds. The majority of documents needed for such tasks can be created using Microsoft’s Excel, because of its resourceful and versatile nature. ...

Read More
Posted Under: CA, CS, CMA |

How to Get Latest Stock Price in Microsoft Excel

Have you ever wanted to have live stock up dates in Microsoft Excel? You might interested to watch Share Market to know current value of your portfolio ...

Read More

How to Use SUMIFS – Advance Excel

Article explains Excel SUMIFS function in 5 different scenario from basic to advance. SUMIFS formula extends abilities of SUMIF function ...

Read More

How to use Two Way Lookup In Microsoft Excel

Most of us aware that Microsoft Excel can add, subtract, multiply, and divide functions. But if we able to use its advance functions like VLOOKUP, HLOOKUP, INDEX+MATCH coupled with IF function, we can able to demeanor DATA MINING within few minutes which generally takes hours to complete the task and sometime impossible if we are dealing ...

Read More
Posted Under: CA, CS, CMA |

Spell Check in Excel –How to Use it?

Excel is one of the common application used in our day to day office life and business like word, Power point, outlook etc. In the case of word or Power point any spell mistake is visible with a red underline below the word that is misspelled. However in the case of Excel we mainly deals with figures and such kind of misspelled word are n...

Read More
Posted Under: CA, CS, CMA |

How to Plot excel data on a Map! [Power View]

With Excel 2010, Microsoft has introduced a cool feature called the Microsoft BI – Business Intelligence tools. One of the tools, PowerView, enables you make some amazing dashboard within a few minutes!...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – Calculate Years, Months, Days elapsed from a certain date in Microsoft Excel

In order to calculate the time elapsed from a certain date, we will use a birthdate as an example. Enter birthday date in cell A1: 01/01/1980. To calculate the number of years from the date, enter the following formula in cell B1: =YEAR(TODAY())-YEAR(A1) . The result (rounded): 23 Years. For a decimal solution, use the following formula: ...

Read More
Posted Under: CA, CS, CMA |

How to suppress 0 values in an Excel chart

Charting 0 Values isn’t wrong, but you won’t always want to display them in you charts. Your data and the chart's purpose will guide you in this decision. When you don’t want to display these values, you have a few choices, and some work better than others. We’ll review a few that offer quick but limited results with minimum effor...

Read More
Posted Under: CA, CS, CMA |

10 powerful ways to use Excel's paste features

Excel’s Paste and Paste Special commands harbor a multitude of useful options. Use them to do everything from transposing data to copying Validation rules. The Paste Special command is in most Office applications, but Excel users probably benefit from it the most. Using this basic feature — as well as assorted Paste options &#...

Read More
Posted Under: CA, CS, CMA |

How to subtract date from date without using excel or any other programming

Common sense way of subtracting 2 dates. At many places students are required to compute number of days, months and years between 2 dates. For example in computing exemption of gratuity duration of service is required. I have seen student calculating duration of service in fingers....

Read More
Posted Under: CA, CS, CMA |

How to Unprotect an excel sheet without password (Updated)

We many times receives excel files from various sources. Sheets of  which are password protected by the author of the file. Sometimes we need to change the content of the file to suit our requirements but we will not be able to  make the changes if the excel sheet is password protected. This document will […]...

Read More
Posted Under: CA, CS, CMA |

Pivot Tables – Excel Tool for Data Analysis

As we are all aware excel is widely used for accounting and financial purposes.In the past they were used only to perform basic moths, such as adding, multiplying, subtracting and dividing. Today's spreadsheets can be significantly different and far more advanced. A CA office cannot work without excel. But do we CA’s utilize excel to it...

Read More

Excel Tips – Moving to the Last (or First) Cell in a Range

Using keyboard shortcuts To move to the last (or first) cell in a range: • Vertically from top to bottom, press Ctrl+Down Arrow. • Vertically from bottom to top, press Ctrl+Up Arrow. • Horizontally from left to right, press Ctrl+Right Arrow. • Horizontally from right to left, press Ctrl+Left Arrow....

Read More
Posted Under: CA, CS, CMA |

Excel Tip – Formatting Cells Containing Formulas

To format cells containing Formulas using the Conditional Formatting dialog box, add a VBA Function: Step 1: Add the following VBA Function to a regular Module: Function IsFormulaInCell (Cell) As Boolean IsFormulaInCell= Cell.HasFormula...

Read More

Excel Tips – Square root and cube root in Microsoft Excel

Getting a root of a number is the same as raising that number to the power of 1/root. Example: The square root of x is x^(1/2). The square root of A1 is: =A1^(1/2). You can also use the built-in worksheet function SQRT: =SQRT(A1) ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – Number of Days, Weeks, Months & Years between Dates

To calculate the difference in days, use the DATEDIF function as shown in the following formula: =DATEDIF(A2,B2,"d") To calculate the difference in weeks, use the INT function as shown in the following formula: =INT((B2-A2)/7) To calculate the difference in months, use the DATEDIF function as shown in the following formula: =DATEDIF(A2,B...

Read More
Posted Under: CA, CS, CMA |

How to Combine VLookup and Match formulas in Microsoft Excel

The VLookup formula returns data from any column you choose in the data table, simply change the number of the column in the third argument. Although it sounds simple, there is a catch. How can you determine the number of a column in a data table that contains numerous columns? How can you easily change the number of the column in...

Read More

Excel Tips – How to round a number to thousands with hundreds shown as decimal

To round a number to thousands with the hundreds shown as a decimal: 1. Press Ctrl+1 to open the Format Cells dialog box. 2. Select the Number tab, and from Category, select Custom. 3. In the Type box, enter the following Custom Formatting syntax: #,##0.0, ;[Red](#,##0.0,);- ; ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Use Vlookup Function

Excel's VLOOKUP function, which stands for vertical lookup, is used to find specific information that has been stored in a spreadsheet table. This tutorial includes step by step instructions of how to use Excel's VLOOKUP function....

Read More

Excel Tips – How to Find duplicates items in combine columns in Microsoft Excel

Find duplicates items in combine columns in Microsoft Excel - Enter the formula : =A1&B1 to cell C1 and copy / paste the formula to cells C2:C12 - Enter the formula : =IF(COUNTIF($C$1:C1,C1)>1,"Duplicate","Unique") - to cell E1 and copy / paste the formula to cells E2:E12 ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Calculate number of minutes between dates – time in Microsoft Excel

Data in cell A1:B2               A            B 1 End Date/Time 05/11/2003 15:33 <– =NOW() 2 Start Date/Time       10/01/2002 9:33 <– =NOW()-222.25 Formula in cell E2: =(B1-B2)*1440    (  24 hours*60 minutes=1440 ) Format in cell E2: regular number format Result:             ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – Formulas to calculate Loan payments in Microsoft Excel

Formulas that calculate loan payments, principal, interest and more are found in the financial category in the Paste Function dialog box. In the screen shot is a list of functions and necessary syntax for loan calculations. See the formulas in the gray cells, and the syntax in rows 12-16. ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Copy Paste only Values from one cell to another?

This formula evaluates to 200. This ‘200’ is the value of Cell B3. To copy and paste this Value i.e. 200, to the adjoining cell C3, go to cell B3, hit [CTRL + C], or right your mouse and select from the shortcut menu that pops up. Go to cell C3 and click  . A [Paste Special] dialogue box shall appear (See figure). ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – Most useful Keyboard Shortcuts

Display or Hide the ribbon? - Press CTRL+ F1.. Go to the last used cell in a sheet? Press CTRL + End. Select cells from any cell to the last cell in the sheet's used range?...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Automatically highlight maximum and minimum values?

Conditional Formatting – Top/Bottom Rules- 1. Select the Range. 2. On the [Home] tab click [Conditional Formatting] à Top/Bottom Rules à Top 10 items…. 3. In the Top 10 items dialogue box specify the number of top items that you wish to highlight and the format for the same. Click ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Automatically format cells (e.g. change font color) based on a certain condition?

Automatically format cells (e.g. change font color) based on a certain condition? Use the conditional Formatting. Conditional formatting allows you to set rules for cell formatting. If the rules (conditions) are met, then the formatting is applied. To do this: 1. Select the cells to be formatted 2. On the [Home] tab of the ribbon click [C...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Combine Text from Multiple Cells?

Combine Text from Multiple Cells? Use the & operator - Lets you combine text from multiple cells ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Split Contents of Cells?

Suppose you have entered the full name of a person in a particular cell and you now want to have Surname, First Name and Middle Name in three different cells. To do this, simply select the cell that contains the Full Name of the person, Click . This will activate the text to columns wizard (See Figure). ...

Read More

Excel Tips – How to Prevent Duplicates while entering data

Use the Data Validation + Countif function. It Prevents the user from entering Duplicate data. To do this: 1. Select a range of cells, for example, A2:A20. 2. On the Ribbon click <Data> à <Data Validation> 3. Select the [Settings] tab....

Read More

Excel Tips – How to create Drop Down list

Haven’t you always wanted to create those lovely drop down list boxes to enter data in lists or forms for uniformity and standardisation? These are called data validation list boxes. Here’s how create them. In our example, Let us consider that you want the user to enter season in column B from a list of valid seasons. To create the Li...

Read More

Excel Tips – alternative to Cell comment

Yes, there is. It’s called Data validation Input message. Select the cell(s) in which you want to see the message. On the ribbon, click the tab then click . Click the [Input Message] tab .Check the option 'Show input message when cell is selected'. Type your message heading text in the Title box. This text will appear in bold print at ...

Read More

Excel Tips – How to insert comment in a cell and how to change the user name in cell comment

Suggest what is to be entered in a cell? (Cell Comment) Cell Comment Suggest the user as regards what is to be entered in the cell 1. Select the cell wherein you wish to see the message, 2. Right click and select . 3. Type the instruction in the comment box. ...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Quickly fill Blank Cells in a list?

Many times in a list, especially so when it is imported from a database, cells down below that have same headings or subheadings are left blank for the sake of easier readability. For eg. See the screenshot below. Excel Tips-3...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Quickly fill a series of values?

Use the AutoFill feature. It inserts a series of values or text items in a range of cells. Drag the AutoFill handle (the small box at the lower right of the Active cell) to copy the cell or automatically complete the series...

Read More
Posted Under: CA, CS, CMA |

Excel Tips – How to Create Shortcuts for commonly used words or phrases?

Create Shortcuts for commonly used words or phrases? Use the AutoCorrect feature. It creates shortcuts for commonly used words or phrases.Click - Files →Options → proofing → AutoCorrect Options. On the [AutoCorrect] tab check the option ‘Replace Text As You Type’ ...

Read More

Browse All Categories

CA, CS, CMA (5,045)
Company Law (6,708)
Custom Duty (8,081)
DGFT (4,386)
Excise Duty (4,406)
Fema / RBI (4,433)
Finance (4,694)
Income Tax (35,069)
SEBI (3,754)
Service Tax (3,626)

Search Posts by Date

November 2020
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30