All articles with 'spreadsheet' Tag
Excel Basics: How to add drop down list to validate data
![Excel Basics: How to add drop down list to validate data](https://chandoo.org/wp/wp-content/uploads/2019/09/excel-drop-down-lists-demo.gif)
Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. Excel drop down list can assist you in picking up a value from a valid list to enter in a cell. Here is a short how-to […]
Continue »![Mutual Fund Portfolio Tracker using MS Excel](https://chandoo.org/wp/wp-content/uploads/2018/07/mutual-fund-tracker-excel.png)
Would you like to spend next 5 minutes learning how to create an mutual fund tracker excel sheet?
Make a live, updatable mutual fund portfolio tracker for Indian markets to keep track of your investments using this example.
Continue »Create a number sequence for each change in a column in excel [Quick Tip]
![Create a number sequence for each change in a column in excel [Quick Tip]](https://chandoo.org/img/n/sequence-number-with-row-change-th.png)
Here is a quick formula trick you can use to generate sequence numbers that only increment when there is a change. Assuming the sequence of values are in column C from C3, you can write the following formula in B4 onwards (B3 will be 1, wake up…) =IF(C4=C3,B3,B3+1) Now just copy paste the formula over […]
Continue »Use Enter to Paste Copied Values in Excel [Quick Tip]
![Use Enter to Paste Copied Values in Excel [Quick Tip]](https://chandoo.org/wp/wp-content/uploads/2008/10/copy-paste-tip-excel.png)
Here is a simple copy pasting tip. Next time when you want to paste a set of copied cells to another area, instead of using CTRL+V, just use ENTER. See this: Learn 16 more excel copy pasting tricks today.
Continue »Sorting a list of items in random order in excel – using formulas
![Sorting a list of items in random order in excel – using formulas](https://chandoo.org/wp/wp-content/uploads/2008/09/shuffling-list-of-items-excel-formulas.png)
In shuffling a list of items in excel I have described the technique of using random numbers generated by RAND() to sort a list of items. The technique had one disadvantage though, every time you need to reshuffle the list you have to press F9 to recalculate the rand() and then go to menu > […]
Continue »![KPI Dashboards – Highlight KPIs Based on Percentile [Part 3 or 6]](https://chandoo.org/wp/wp-content/uploads/2008/09/kpi-dashboard-3-thumb.jpg)
Creating KPI Dashboards in Microsoft Excel is a series of 6 posts by Robert from Munich, Germany. This 6 Part Tutorial on KPI Dashboards Teaches YOU: Creating a Scrollable List View in Dashboard Add Ability to Sort on Any KPI to the Dashboard Highlight KPIs Based on Percentile Add Microcharts to KPI Dashboards Compare 2 […]
Continue »![6 charts you will see in hell](https://chandoo.org/wp/wp-content/uploads/2008/09/6-charts-you-must-avoid-excel.jpg)
Often it is easy to get carried away with a tools features. Excel is no exception. But here is a list of grotesque charts that you should never make, not even on your last day at work. 1. Leave the radar charts for Spidermen why? You can hardly conclude anything by looking at them They […]
Continue »Aligning charts, objects on spreadsheet [excel layout tip]
Often when you are adding several charts to your report or project plan, it may be important to have them all aligned to a proper baseline – a fundamental design principle. Take a look at this: But if you try to select each chart individually and adjust the alignment, it may not provide correct results […]
Continue »Creating KPI Dashboards in Microsoft Excel is a series of 6 posts by Robert from Munich, Germany. This 6 Part Tutorial on KPI Dashboards Teaches YOU: Creating a Scrollable List View in Dashboard Add Ability to Sort on Any KPI to the Dashboard Highlight KPIs Based on Percentile Add Microcharts to KPI Dashboards Compare 2 […]
Continue »Excel Links of the week – What do you dream about? [Aug 19]
The other day my wife told me that we dream about a total of 6 years in our life, an average of 2 hours for everyday you sleep. [Source: wikipedia on dreams]. That is almost as much time as you would work in 30 years (30*50*40/24/365, assuming you work 40 hours a week for 50 […]
Continue »![Beyond If and Sum, 15 really useful excel formulas for everyone](https://chandoo.org/wp/wp-content/uploads/2008/08/type-formula-arguments-spreadsheet.png)
Excel formulas can always be very handy, especially when you are stuck with data and need to get something done fast. But how well do you know the spreadsheet formulas? Discover these 15 extremely powerful excel formulas and save a ton of time next time you open that spreadsheet. 1. Change the case of cell […]
Continue »I use google suggest to get new post ideas, I start out by typing “what is”, “who is”, “how to”, “when is”, “where is”, “excel” and see all the suggestions Google comes up with. If I see something interesting I would mark the search topic and use it for posting here. And for the last […]
Continue »Use min-max charts to show the spread of data – Charting Best Practice
Next time you want to make a chart to tell how your sales (defects, customer calls, page views, number of clicks, walk-ins etc.) are doing over a period of time, consider showing them in a min-max chart. For eg. a min-max sales chart for the last 12 months tells average, minimum and maximum sales per […]
Continue »Excel links of the week : ‘what can excite you most’ [Aug 4]
Passion, excitement, inspiration or love – whatever you choose to call it, is the key reason we do remarkable things. Take a minute break here, think about the most significant moments in your life. What is common in all of those moments? I am sure your answer will be Passion or excitement, love, inspiration. I […]
Continue »Excel can be Exciting – 15 fun things you can do with your spreadsheet in less than 5 seconds
![Excel can be Exciting – 15 fun things you can do with your spreadsheet in less than 5 seconds](https://i287.photobucket.com/albums/ll133/pointy-haired-dilbert/excel-exciting-15-fun-things/8-fetch-stock-quotes-live.png)
Who said Excel takes lot of time / steps do something? Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5 seconds to do. Happy Friday 🙂 1. Change the shape / color of cell comments Just select the cell comment, go to […]
Continue »