All articles in 'Learn Excel' Category

Excel Links of the Week [Sep 29]

Published on Sep 29, 2008 in excel links
Excel Links of the Week [Sep 29]

Time for sharing another round of excel related articles around the web. List of all data visualization and analysis excel add-ins, The list includes some of the really useful add-ins. Handy bookmark if you work with data visualization alot and need to know where to find good stuff to add to your excel capabilities. Designing […]

Continue »

Handling spelling mistakes while searching your data using excel

Published on Sep 25, 2008 in Analytics, ideas, Learn Excel
Handling spelling mistakes while searching your data using excel

Spelling mistakes are a thing of day to day carporate life. Most of the data in spreadsheets is entered by people and hence prone to having spelling mistakes or alternate spellings. For eg. a person named John could have been spelled as Jon. And when John calls you back to confirm his reservation and you […]

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 »

SQL Queries from Excel

Published on Sep 22, 2008 in Learn Excel, technology
SQL Queries from Excel

Often when you are working on 2 incompatible systems and try to import data from one to another, excel can be a handy tool. I have used excel plenty of times to generate SQL insert / update statements which I could later execute on the database console. Here is a tutorial if you ever have […]

Continue »

Weekly Excel Links – Moved to Seattle Edition

Published on Sep 22, 2008 in Charts and Graphs, excel links, Learn Excel

As mentioned earlier we have moved to Seattle this Sunday. It seems like a great place already. Just that everything is more expensive than we are used to in Columbus. But I guess that is the premium we have to pay for being in a big city. On to this week’s edition of excellent excel […]

Continue »

You are NOT spider man, so why do you use radar charts?

Published on Sep 18, 2008 in Charts and Graphs, ideas, Learn Excel
You are NOT spider man, so why do you use radar charts?

There are few charts in excel that are as revolting as a radar chart. The purpose of a radar chart is to compare m options across n parameters so that audience can be convinced that option A is better than say option B. But, I am yet to come across a radar chart that tells […]

Continue »

Visualization Challenge – How to show market share changes?

Published on Sep 18, 2008 in Charts and Graphs, Learn Excel
Visualization Challenge – How to show market share changes?

One of our readers Jennifer mailed me with this excel charting problem she is having. I thought why not ask other readers for their advice on this. So here it goes: The Charting Challenge – Visualizing Changes in Market Share Here is an excerpt from her email: The gist of my problem is how to […]

Continue »

Calculating Paydays in a calendar year using Excel

Published on Sep 17, 2008 in america, Learn Excel
Calculating Paydays in a calendar year using Excel

In India salary is usually paid on the last working day of a month – the payday. It is slightly different in countries where payrolls are processed every 2 weeks. For eg. in US most companies pay salary on every 2nd Friday / Thursday. We can calculate the paydays / payroll periods in excel with […]

Continue »

Web Page Monitor using Google Docs

Published on Sep 16, 2008 in ideas, Learn Excel, technology
Web Page Monitor using Google Docs

My guest post on monitoring web page changes using google docs is posted in Digital Inspiration – one of the most popular technology blogs. Do read it to learn a little bit about using XPATH, RSS Feeds, Google Spreadsheet import() functions. Here is a summary of the technique: First go to the web page which […]

Continue »

Power Outage & 99 Excel Tips to keep you Busy

Published on Sep 16, 2008 in ideas, Learn Excel

We have a major power outage in Ohio due to a wind storm on Sunday. AEP, Our electric provider says that power would be restored to my area by this Friday midnight, that is almost 6 days without power. I will not be able to blog regularly during this period. So here is a list […]

Continue »

Tracking your stock portfolio using Google Docs

Published on Sep 12, 2008 in ideas, Learn Excel, personal finance
Tracking your stock portfolio using Google Docs

By integrating services like Google Finance, Search in to spreadsheet functions, google docs has paved the way for endless possibilities. Google spreadsheet function GoogleFinance() can be used to build stock portfolio tracker sheet that can fetch historical stock quotes to tell how your money is doing. Click here to see the stock portfolio tracker sheet […]

Continue »

Excel KPI Dashboards – Adding Micro Charts [Part 4 of 6]

Published on Sep 10, 2008 in Charts and Graphs, Featured, Learn Excel
Excel KPI Dashboards – Adding Micro Charts [Part 4 of 6]

This is 4th part of Creating Management Dashboards in Microsoft Excel 6 post series by Robert. This 6 Part Tutorial on Management 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 KPIs […]

Continue »

Hack together a Gauge Chart in Excel without sweat

Published on Sep 9, 2008 in Charts and Graphs, hacks, Learn Excel
Hack together a Gauge Chart in Excel without sweat

Gauges are a familiar metaphor, everyone can understand them, you can see them everywhere – near your stove, ac, car, gaming console, pc – you name it. So, when you are preparing a chart to tell a point, gauge chart like the one above can be effective. (I know charting pros like Jon Peltier wouldn’t […]

Continue »

Excel links of the week [Sep 8]

Published on Sep 8, 2008 in excel links

Every week Pointy Haired Dilbert features some of the excel links from around the web here. Get the archives here. Teaching physics using excel My friend Jon @ PTS Blog tries a different approach to teach physics to his daughter. Who says excel is only for corporates? Daily dose of Excel compiles the ultimate list […]

Continue »

Splitting text in excel using formulas

Published on Sep 8, 2008 in ideas, Learn Excel
Splitting text in excel using formulas

Often when you are processing text using excel it is important to split the text in to multiple parts based on a delimiter. For eg. you may want to divide this|needs|to|be|split in to five parts this needs to be split. There is a simple way to do this in excel, using “import text” option. But […]

Continue »