Recently, I have been working with my team on a client’s project. The client wanted a web based database application.
The client gave us large sets of data in Excel sheets, and then the data had to be restructured to a new format that will work with our app.
Working on this project reminds me of my love for Excel and its magical power. I have been busy doing many other things so I haven’t had the reason to dig into Excel until lately. But as I always knew, every challenge has a solution and a better way, so I am that type who always look for a more effective way to solve problems.
There are basic simple things that you can all do to improve your Excel knowledge and to help you work faster in any version of Excel. I am mentioning 4 major things we did on this project that tremendously helped to save time. When your formula is right, you don’t need to worry about data accuracy. In any case, for me, I usually still double check to be sure that my results are as expected.
- The project required that we change the date format so it can fit into our new program: this formula will help change any date format in Excel: =TEXT(A17,”yyyymmdd”) – where A17 represents the cell that contains the old date format.
- The project required that we layer data that were in different columns into a single column, (even if the column ranges from A to XY), this command will help you achieve that: =OFFSET(GDPTable,MOD(ROW()-ROW(MergedData),ROWS(GDPTable)),TRUNC((ROW()-ROW(MergedData))/ROWS(GDPTable),0),1,1). For this to work, you will need to first name the range of cells where the data is (in the formula above, the name of the data table is, GDPTable); and then name the first cell where you want the result to appear (in this case MergedData). Once you have done that, copy the code above into the first cell in the column where your results should appear and then press the enter key. The first result appears in the cell, you can autofill until the entire result for the table is now restructured into one column. Read this post on how to define names or follow this method if you are using office 2010 and newer versions. Select the Data Range, Click on the Formula Tab, Select Define Name.
- The project required that we change data in rows into columns and vice versa: the Excel transpose function will help you achieve that. The formula for that is =TRANSPOSE(B11:B21); where B11:B21 is the range of the original cells, close the brackets and then press CTRL+SHIFT+ENTER.
- And lastly, we needed a clean number range without scientific notations (MS Excel by default changes all numbers in a cell that has more than 12 digits to scientific format e.g. 2.97E+24). To show the entire number, Just select the columns or rows with the numbers, right click, click on format cell, and then select number.
Have fun experimenting with MS Excel!
XX
STB