Some Excel Hacks and Tips

Recently someone asked about some Excel hacks and tricks on Quora. These are some of tips I quickly came up with

Note: Here I will not teach ‘technically-how’ to implement these tricks in Excel. I will explain them conceptually and you can learn the technical stuff on your own. Who knows you may discover a couple new tricks while playing around.

1. Keep it Dynamic
Nothing is sexier than keeping things dynamic in Excel. You provide some input and the output changes based on your input. A chart, a data-set, a predictive model, a dashboard, a table, a linked-image, a look-up directory – almost anything could be designed to be dynamic in Excel. Here is an example.
My goal is to present a chart for multiple locations and I have three different locations. I can do something like this and produce three different charts for each location:

Bar Chart in Excel

What if the locations were 20 instead of just 3. Real horror for development work. Consistency of formatting issue. Screen space problem. And the end user could get confused on which-is-which and so on. How about reducing your problem to just one dynamic problem and solve it in some this way:

Drop Down Data Validation Bat Chart in Excel

You could do such sort of things using Data Validation feature of Excel combined with OFFSET or VLOOKUP function.

2. Use RANDBETWEEN to Simulate Something Quickly
If the Excel if really at core of your heart then you would require to do a lot of quick things that you will discard later.
Like I needed to create this chart above 🙂 I was really not worried about the data. I simply simulated it using RANDBETWEEN function and some 7th grade maths.

RANDBETWEEN Simulation in Excel

You can also roll a dice or flip a coin using RANDBETWEEN, right 🙂

3. Use Font in Decimal Size
I think Calibri 11 is too cluttered and 10 too small. Therefore, the font I have used in table in the above picture is Calibri 10.5 and I quite love it. And some of my peers too.
Just go to the font size box and type any decimal number and hit return key. May be you also will find your favorite font for next 10 years.

4. Custom Format Things
Month Format in Table – Jan-14, Feb-14, Mar-14, Apr-14…
Month Format in Chart – J, F, M, A…
That is the power of Ctrl+1 and format the text in custom format. Here the one I have used is mmmmm to convert any month to the first letter of that month

5. Tweak Around Format Series Option of a Chart
Creating a chart is far beyond than actually presenting a nice beautiful data visualization. You will definitely experience this if you are using any Excel version lower than 2013.
This is what you get by default in Excel 2007

Default Bar Chart in Excel 2007

Until you format it to something like shown above in trick 1

6. Use Color Formatting to Make It Visually Interesting
Do you see that table above I shared with you. Its alternative rows are colors in light shades contrasting with dark font colors. All text aligned left with 1 indent and all numbers aligned right, also with 1 indent. Remember – that 1 indent is very important for a clean look of table when it has multiple columns.
P.S. Border of table should be light and a shade darker than cell fill color.

7. And the Final and Most Important Tip
This is a little irreverent but hope you will tolerate me with this one 🙂
If you really want to become a data analyst regardless whether you store your data in Excel or Hadoop, you need to be skeptical. Do not just get the data and start running your learned tricks and tips on it straight forward. Always ask questions like

  • Why I should trust this data
  • My null hypothesis about this data is that this data is wrong, and now I will try to test if my null hypothesis can be rejected
  • What are some interesting patterns in data (Oh ! Sales of umbrella goes high each July)
  • Why are those interesting patterns (Because it rains in July)

And there can be lots of such things. You will keep learning more of them as you go. I hope the tricks and thoughts I have shared here with you help you in some way.

If you also have some tips and tricks to share, please comment below 🙂

Advertisements
Some Excel Hacks and Tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s