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

How to Convert A String Containing Dash into Number

PROBLEM STATEMENT

I have a text string in format 9876-54321. How can I convert it from text to number format like 987654321?

APPROACH

Lets think of a number that is made of numbers and dashes. Lets call it MyDashedNumber and it looks something like 565757-55855—5755—-7210. We want all the dashes gone. Our approach could be to replace all dashes with a NULL string.

SOLUTION

=(SUBSTITUTE(TRIM(MyDashedNumber),"-",""))*1

HOW DOES IT WORK

TRIM(MyDashedBumber) TRIM removes any space from MyDashedNumber. I am recommending TRIM to use a to be on safer side just in case if there is any additional space in the string that you are trying to convert into a number.

SUBSTITUTE As the name itself suggests, This function is used to substitute some text from your string and replace it with a newer one specified by you. In our case, we are substituting “-” with nothing “”. Therefore this formula will remove all dashes “-” and will add nothing to string.

Multiply the Result with 1 SUBSTITUTE always returns the result as a text string (even though it is made of numbers). In Excel, we can multiply any string that is made of numbers (0,1,2,3,4,5,6,7,8,9) with 1 and convert it from a string to a number format.

Simple Solution Alternatively, you could select all the cell where you want to convert MyDashedNumber into numbers and use Find and Replace dialogue box (Ctlr+H) and Replace All dashes (“-“) with nothing ().

Find-and-Replace-Microsoft-Excel

There you go 🙂

How to Convert A String Containing Dash into Number

History of Microsoft Excel

History of Microsoft Excel exists since 1979 with the development of VisiCalc. It is not necessary to know history of Excel for every user however, if you are deeply interested in Excel and want to know how it all started and what transformations in the software were done over the period, this article might be a must read for you.

History of Information Management

Since the time trade started in the history, tradesmen needed an organized way to manage their information. In the ancient times when there was no industrialization and businesses were small in size and scope, this task was done in the memory of brain. Around 10th to 15th century, with the knowledge of Arabic Numerals, business information management shifted to pen and paper and this gave birth to ledger and accounts. As the scope and magnitude of business grew, so grew the need of more organized solutions for the business. In the last decade of fifteenth century, Double-entry bookkeeping system changed face of the account management. It was an accounting system that was run by few set of rules to record financial information. It could manage debit/credit, profit/loss, asset/liability, expense/income type of accounts.

Why was a spreadsheet program needed?

The second half of twentieth century has been a great revolution in itself. In these 50 years things happened that were mere a dream earlier. IBM had already brought a revolt in the business world with first their computer 701 and later IBM-PC in 1981. Since computers were super-expensive those days, only big-level enterprises and government organizations shown up their interest. These big businesses had heaps of information and it was becoming difficult to manage this information manually. Now the greave need was something that was easier, centralized and top of all not manual.

VisiCalc – World’s First Spreadsheet Program

Microcomputers existed there by that time but there were no serious business solution tool those days. Business world felt a need of solutions those could run on computers and make the computer a real productive thing. And this need was fulfilled in 1979 by a company named Software Arts who developed world’s first spreadsheet program VisiCalc for Apple II computers. You can understand immense success of VisiCalc by the fact that this program sold 700,000 copies in first six years (Think till 1985 computer was an expensive business tool and not every kid used it). VisiCalc was divided into columns and rows which created grids at the intersection point. This column-row concept was so influential that even today’s spreadsheet programs use this concept. Information was stored in these grids and a mathematical and financial calculation could be performed in these grids. This program amazed the world and simplified the task.

And then Lotus 1-2-3 overtook the industry for a whole decade

IMB-PC was out there in 1981 and VisiCalc still ran on Apple II. In 1983, 1-2-3 by Lotus Software was released and immediately overtook the VisiCalc market. Lotus 1-2-3 was one of the core reasons of huge success of IMB-PC in mid-1980s. Since the VisiCalc was too basic while Lotus 1-2-3 offered a wide range of functionality, Lotus 1-2-3 sold like hotcakes. Along with as a spreadsheet solution, it could do charting/graphing and perform some basic database operations too. Because of these three powerful functionalities it was named Lotus 1-2-3. The program was a huge success and many imitators were inspired and launched their spreadsheet programs in market but none of them could compete the software. And Lotus 1-2-3 remained undisputed and unchallenged software in the market for a decade until Microsoft’s Windows became popular and provided a platform to their spreadsheet software Excel.

And here comes the Microsoft Excel in the Picture

Microsoft launched themselves in the business of spreadsheet in 1982 with their product Multiplan as a competitor to VisiCalc. Multiplan was also known as EP (Electronic Paper). But as we know, Lotus 1-2-3 outsold VisiCalc. Though Multiplan was popular on CP/M systems, it did not have so much to challenge on MSDOS platform to Lotus 1-2-3. Though Multiplan was not a long term success, it was a milestone in history of Microsoft (and of course in history of spreadsheets too) because this is what influenced Microsoft to develop one of the greatest product of the era – Microsoft Excel.

Microsoft launched first version of Excel in 1985 for Macintosh. But the actual success of Excel started two years later when Microsoft launched the first version of Windows and a Windows version of Excel was released. Windows was getting popular and started to overtake others OS. But since Lotus were not too quick on development of 1-2-3 for Windows, Excel sold for Windows system and made Microsoft one of leading competitors in the software world.

History of Microsoft Excel

Microsoft kept themselves at the top in the competition by bringing an updated version of the software every alternative year (or so). With every new release the product kept on becoming more capable and powerful. Version Excel 5.0 was released in 1993 and it integrated Visual Basic for Applications (VBA). Though VBA could do wonders in Excel from automating a repetitive task to develop amazing applications based on Excel, it opened a gate for VBA based virus attack.

Following version was Excel 95 which was a copy of Excel 5.0 from 16 bits to 32 bits and it provided a better stability, speed and performance. Excel 95 also included one of the most famed Easter-egg ‘Hall of Tortured Souls’ (We shall later discuss this as a separate topic) which was fixed by Microsoft in Excel 2002 (version 10).

Next version was Excel 97 and brought up some major changes which included Clippy Office Assistant and Standard VBA. User forms, data validation were some more new features of this version.

In 1999, Microsoft launched Excel 2000 for Windows as a part of Microsoft Office 2000 and this version could hold more objects on clipboard and showed up Clippy Office Assistant wore wisely which earlier annoyed more often. Pivot Charts were added in this version which prior version hold capability of PivotTables only.

Excel XP was out in 2001 and this version could recover lost files on itself. 2 years later in 2003 the new version Excel 2003 was released. This was last version to hold Menu commands. Most important feature of this version was List (also known as Table in Excel 2007 and later versions).

Microsoft Excel 2007

And this was end of old followed traditions. Microsoft broke the rules of drop down menus and introduced first ribbon based GUI for Microsoft Office. It was a revolutionary change in itself. Many were just overwhelmed by these major changes while some just found it completely confusing and were disappointed. Excel used a different file extension .xlsx (in earlier versions it was .xls). Now user could save files in binary format (.xlsb). A Binary Format offers an improved performance with significantly reduced file size. It stores a single workbook spilt in many binary files which are compressed together. Table took place of List. Real-time preview was another important feature of this new Excel and simply mesmerized users. Earlier users had only 65,000+ rows and now they had 1M+ rows and 16,000+ columns. This change was really major because in previous version users faced huge challenges while dealing with large data. Where Excel 2003 was using 1 GB of system memory, Excel 2007 was using 2 GB memory and this increased memory usage speeded up Excel enormously. In nutshell, Excel 2007 was introduced with many major modifications that completely changed user experience with software. We shall discuss in details later about these modifications.

Microsoft Excel 2010

Since Excel 2007 was a ground-breaking effort and completely different from its prequel Excel 2003, there were things which were not perfect. They needed a better finishing, the world was experiencing a new era of Cloud Computing; Excel once again needed a rebirth. And the output was just so amazing. Yes! Excel 2010 was capable of doing new amazing things. People could summarize their data by a chart built-in within a cell. PivotTable achieved new heights with features like Slicers and PowerPivot. Data summarization was easy like never before. Sharing and mobility were redefined in this new version. Conditional formatting, new added functions, filtering and sorting were enhanced for a rich experience. And of course there was a first time ever new 64 bit version. This 64 bit version could handle massive amount of data; can you imagine Excel handling files up to 2 GB size. This is just best one could get out of their investment. Remember! When was it last when you were working on an unsaved spreadsheet and you ran out of power and lost all you hard efforts. This was no more a thing to worry in Excel 2010. Yes, you heard right. Microsoft Office Backstage™ view is capable of recovering your unsaved files. These changes are so significant that we shall discuss about them in a separate post later.

Microsoft Excel 2013

Microsoft launched Office Suite 2013 in end of 2012. With more features like inbuilt PowerPivot, Power Query and Power View, Excel 2013 has come up with some amazing functionality.

Microsoft Excel has been undisputed player in spreadsheet market for last two decades. There are products like Google Docs, Open Office and IBM’s Big Sheets but Excel has its own place in the industry. Success has not come to Microsoft just like that. It is result of constant innovation and development that they have kept doing to keep the Excel updated and ready for new challenges. So we hope to see much more in Excel in next years.

History of Microsoft Excel