All articles with 'spreadsheet' Tag

Create a number sequence for each change in a column in excel [Quick Tip]

Published on Jun 29, 2009 in Excel Howtos, Learn Excel
Create a number sequence for each change in a column in excel [Quick Tip]

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]

Published on Oct 17, 2008 in Learn Excel
Use Enter to Paste Copied Values in Excel [Quick Tip]

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

Published on Sep 23, 2008 in Analytics, Learn Excel
Sorting a list of items in random order in excel – using formulas

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]

Published on Sep 3, 2008 in Charts and Graphs, Featured, Learn Excel
KPI Dashboards – Highlight KPIs Based on Percentile [Part 3 or 6]

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

Published on Sep 3, 2008 in Charts and Graphs, Featured, Learn Excel
6 charts you will see in hell

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]

Published on Aug 28, 2008 in Learn Excel

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 [Part 2 or 6] – Adding One Click Sort

Published on Aug 27, 2008 in Analytics, Charts and Graphs, Featured, Learn Excel

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]

Published on Aug 19, 2008 in ideas, Learn Excel

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

Published on Aug 13, 2008 in All Time Hits, Featured, ideas, Learn Excel
Beyond If and Sum, 15 really useful excel formulas for everyone

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 »

Excel Links of Week – Who is Obama edition? [Aug 12]

Published on Aug 12, 2008 in ideas, Learn Excel, wonder why

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

Published on Aug 11, 2008 in Charts and Graphs, Learn Excel

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 Basics: How to add drop down list to validate data

Published on Aug 7, 2008 in Learn Excel

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. In-cell drop-down lists or combo boxes can assist you in picking up a value from a valid list to enter in a cell. Here is a […]

Continue »

Excel links of the week : ‘what can excite you most’ [Aug 4]

Published on Aug 4, 2008 in Learn Excel

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

Published on Aug 1, 2008 in All Time Hits, Featured, hacks, ideas, Learn Excel
Excel can be Exciting – 15 fun things you can do with your spreadsheet in less than 5 seconds

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 »

11 very useful excel keyboard shortcuts you may not know

Published on Jul 29, 2008 in Learn Excel
11 very useful excel keyboard shortcuts you may not know

– During formula typing, adjusts the reference type, abs to relative, otherwise repeats last action + – Inserts current date +

Continue »