All articles with 'excel 2007' Tag
Offset() function to Calculate IRR for Dynamic Range
Offset() function to Calculate IRR for Dynamic Range When you start the project can you be sure, for how long will you operate it? A VC gives you funds to buy a commercial project. You are to operate the project for some time and then sell it off! Can you tell me today, when you […]
Continue »Accumulated Depreciation using Mixed References
![Accumulated Depreciation using Mixed References](https://chandoo.org/wp/wp-content/uploads/2011/07/image_thumb3.png)
Last time we had discussed the use of SumProduct() to ease your life for calculation of consolidated revenues and depreciation. This time we would be using the sum function! Yes you heard it right – The Sum function.
But we would use the Sum function with a small trick! We would use it to calculate running cumulative sum! And believe me, you would need this function so many times – to calculate accumulated depreciation, cumulative debt, Profits to Retained Earnings and almost all the accounts that would consolidate into the balance sheet.
Continue »Sumproduct function to Consolidate Revenues?
Chandoo.org is all about simplifying life using Excel. When I first started visiting the site, I was amazed at the amount of information on the site, which made your life easier. My next few posts would be about excel functions which can make your life for Financial Modeling easier! These are simple examples that you […]
Continue »Convert ISERROR formulas to IFERROR formulas [macro]
![Convert ISERROR formulas to IFERROR formulas [macro]](https://chandoo.org/img/vba/iferror-to-iserror-to-iferror.png)
Last Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel workbooks.
Quite a few people reading that page asked, “Wow, this is good. But how can I take a sheet full of =IF(ISERROR(…)….) formulas and convert them to =IFERROR()”
There is a different set of folks who asked “Wow, this is good. But quite a few of my colleagues use Excel 2003 and they see a bunch of #NAME errors when I send them an excel workbook with IFERROR formulas. Any help?!?”
I am pleased to announce that I wrote 2 simple macros, iferror2iserror() and iserror2iferror() that would scan formulas in a bunch of selected cells and convert them from IFERROR to ISERROR and vice-a-versa.
Continue »IFERROR Excel Formula – What is it, syntax, examples and howto
![IFERROR Excel Formula – What is it, syntax, examples and howto](https://chandoo.org/img/f/iferror-excel-formula-example-syntax.png)
If IFERROR()
were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.
Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.
Continue »Save a ton of time by using Mouse scroll-wheel to Navigate Excel Ribbon
![Save a ton of time by using Mouse scroll-wheel to Navigate Excel Ribbon](https://img.chandoo.org/q/ribbon-with-mouse-scroll-wheel-th.png)
Today’s quick tip is by far the quickest and most profitable tip of all. To switch between ribbon tabs or to change to a particular tab, you can use mouse scroll-wheel. Just place mouse pointer on a ribbon tab, and scroll-down or up using your mouse’s scroll-wheel and you can switch between tabs. Learn more […]
Continue »Format Charts Faster in Excel 2007 [quick tips]
![Format Charts Faster in Excel 2007 [quick tips]](https://chandoo.org/img/c/quick-chart-formatting.png)
Most of us use chart formatting options to change the way grid-lines, data series, labels, axis, titles, plot areas look. Chart formatting is one of the areas where people spend most time. Today I want to teach you a quick productivity hack to speed up chart formatting. In excel 2007 and above, when you click […]
Continue »![10 Tips to Make Better and Boss-proof Excel Spreadsheets](https://chandoo.org/img/l/make-better-excel-sheets-th.png)
We all have atleast one story of how that one time the boss / co-worker / classmate / cat ruined the carefully crafted excel spreadsheet by mucking up the formulas or disturbing the formatting. Read this post to learn 10 awesome excel tips to make better and boss-proof excel sheets.
Continue »Making a chart with dynamic range of values
![Making a chart with dynamic range of values](https://chandoo.org/img/c/dynamic-chart-data-series-th.png)
We all know that to make a chart we must specify a range of values as input.
But what if our range is dynamic and keeps on growing or shrinking. You cant edit the chart input data ranges every time you add a row. Wouldn’t it be cool if the ranges were dynamic and charts get updated automatically when you add (or remove) rows?
Well, you can do it very easily using excel formulas and named ranges. It costs just $1 per each change. 😉
Ofcourse not, there are 2 ways to do this. One is to use Excel Tables and another is to use OFFSET formula.
Continue »What buttons you have in the excel quick access bar ? [weekend poll]
![What buttons you have in the excel quick access bar ? [weekend poll]](https://chandoo.org/img/i/quick-access-bar-excel-2007.png)
This week’s poll is quite simple. Tell us what buttons/ icons you have added to the quick launch bar in excel 2007 (or toolbar in excel 2003)?
As for me, I have these in my home laptop’s excel:
Just use the comments and tell me about your settings.
Continue »Use Cell Styles to Make your Spreadsheet Models User-friendly [Quick Tip]
![Use Cell Styles to Make your Spreadsheet Models User-friendly [Quick Tip]](https://chandoo.org/img/i/use-excel-cell-styles.png)
Spreadsheet modeling or scenario modeling is one of the common uses of Microsoft Excel. People, especially in financial sector use MS Excel to do a lot of modeling. While excel has such powerful features like goal seek and scenarios, it also has a very useful feature called “cell styles” that you can exploit to make […]
Continue »7 ideas Excel can pick-up from iWork Numbers
![7 ideas Excel can pick-up from iWork Numbers](https://chandoo.org/img/n/excel-vs-iwork-numbers.png)
We have a macbook at home. Like all latest macbooks, this one too came with a trail version of iWork. Even though I have used iWork before, this time I wanted to compare iWork numbers with Excel. In this post, I want to highlight 7 really cool features for iWork and how Microsoft excel can benefit from implementing the same.
Continue »Master Excel 2007 Ribbon with this Free Learning Guide
Over the last few years, there has been much debate about the merits and perils of Microsoft Ribbon UI in Excel 2007. Personally I think ribbon is a good way to explore an application. I have gotten used to it since I tested excel 2007 for first time. Now, during the rare occasions I work […]
Continue »Pin Frequently Used Documents to File Menu to Save Time
![Pin Frequently Used Documents to File Menu to Save Time](https://chandoo.org/img/p/pin-documents-in-recent-list-th.png)
Here is a quick productivity tip if you work with same set of excel (or word or powerpoint) documents everyday. Just pin the documents to recent documents list in the Office Menu in MS Office 2007. See this: More excel 2007 productivity tips & double click tricks.
Continue »![Hide Grid Lines [Quick Tip]](https://chandoo.org/wp/wp-content/uploads/2009/08/hide-gridlines-excel-workbook.png)
I like to hide grid lines on my spreadsheets and charts whenever possible. I think removing gridlines makes the charts and worksheets more presentable. In case you are wondering how to remove (or hide) gridlines from your worksheet or chart, follow these simple instructions.
Continue »