Relative vs. Absolute References in Formulas [spreadcheats]

on 4 Nov, 2008 in Excel Tips | 5 Comments

Relative vs. Absolute References in Formulas [spreadcheats]

This is the first installment of the spreadcheats series.
I have used excel an year and half before I learned about the relative and absolute references. It was such a joyous feeling to find out that you can actually write one (or just a few) formulas and use the power of auto-fill to do the dirty [...]

Read More >>

Excel Links of the Week [Nov 3]

on 3 Nov, 2008 in excel links | 1 Comment

The World’s First Music Video in Excel ACDC Rock n Roll Train
This is totally cool. ACDC released an ASCII version of their “rock n roll train” video through excel spreadsheet. Why? So that you can download it at your office and watch it while your boss thinks you are working. I think this is [...]

Read More >>

Adding Box Plots to Show Data Distribution [Excel Dashboard Tutorials Part 2]

on 29 Oct, 2008 in Analytics, Excel Tips, visualization | 3 Comments

Adding Box Plots to Show Data Distribution [Excel Dashboard Tutorials Part 2]

This is a Guest Post by Robert on Visualization Techniques for Excel Dashboards.
Background
With the dashboards using excel - 4 post series here at Pointy Haired Dilbert we developed a small excel based dashboard that enables the user to scroll within a larger set of data, to sort by a selected KPI with one click, to [...]

Read More >>

Sorting Text in Excel using Formulas

on 22 Oct, 2008 in Analytics, Excel Tips, Featured | 13 Comments

Sorting Text in Excel using Formulas

Sorting text is such a day to day activity that it always surprises me why Excel hasn’t provided a simple spreadsheet formula for doing it. Of course you can use the sorting menu command (menu > data > sort) but this requires manual steps (or VBA). Most of the times we get raw textual data [...]

Read More >>

SQL Insert / Update statements from CSV files

on 22 Sep, 2008 in Excel Tips, technology | 4 Comments

SQL Insert / Update statements from CSV files

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 [...]

Read More >>

Calculating Paydays in a calendar year using Excel

on 17 Sep, 2008 in Excel Tips, america | 2 Comments

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 [...]

Read More >>

Power Outage & 99 Excel Tips to keep you Busy

on 16 Sep, 2008 in Excel Tips, ideas | 1 Comment

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 [...]

Read More >>
Page 2 of 3«1[2]3»