Data Layouts Worksheet Final: Fill & Download for Free

There are two main things you want to pay attention to here:The layout of the actual table (like headers and alignment and borders)The formatting of the actual dataLet’s look at a quick example. Let’s say you have this raw data as a starting point:1. Layout of the tableHere are the things I do when presenting data to people in a business environment:Make the headers of the table boldI’ll underline the header column(Optional) - some people remove the gridlines (those squares all over the sheet) in the worksheet under the layout tab, but I don’t like to do that and will exclude it hereHere’s where we are at so far:2. Formatting of the actual dataMake sure all numbers are aligned to the rightMake sure numbers have the right number formats (dollars have $ signs in the accounting number format, percentages have % signs…etc)Pro tip - round big numbers (over ~10) to 0 decimal points using the “decrease decimal” feature. You never want to see $1,002.73, always round to $1,003. The cents don’t matter!Here’s the final product:This is a very presentable table. Now you’re a pro!-If you’re interested to learn the core of becoming a power-user (including a whole module covering basic through advanced formatting), take a look at my video-based course on Udemy.Become an Excel Power User in 2.5 Hours | UdemyHope this helps!

Good question. It is hard to know exactly what to do without someone showing you how we present things in business.So, I‘ll give you a head start using the following two examples!Formatting a data tableFormatting a financial model with “freeze panes”Ok let’s get started!Example 1: formatting a data tableLet’s say you have the following data table that you want to present to your boss.There are two main things you want to pay attention to here:The layout of the actual table (like headers and alignment and borders)The formatting of the actual data-1. Layout of the tableHere are the things I do when presenting data to people in a business environment:Make the headers of the table boldI’ll underline the header column(Optional) - some people remove the gridlines (those squares all over the sheet) in the worksheet under the layout tab, but I don’t like to do that and will exclude it hereHere’s where we are at so far:2. Formatting of the actual dataMake sure all numbers are aligned to the rightMake sure numbers have the right number formats (dollars have $ signs in the accounting number format, percentages have % signs…etc)Pro tip - round big numbers (over ~10) to 0 decimal points using the “decrease decimal” feature. You never want to see $1,002.73, always round to $1,003. The cents don’t matter!Here’s the final product:This is a very presentable table.Example 2: format a financial model using freeze panesOften you have a financial model or a larger sheet you need to present.Something like this.But, lets say we want to scroll down to row 38 to see our net income.This presents a problem.We lost our headers. We can’t see what year the data is in.So to fix this, we can Freeze Panes.Move the cursor to cell B2. Everything above and to the left of B2 will Freeze!Then, navigate to the Viet tab and click freeze panes.Check this out. When I scroll down now, the headers AND first column stay frozen.The benefit of this feature is that your user won’t get disoriented somewhere in the model.They will always know which year they are looking at, and which account (row) they are in, no matter where they scroll to in the model.Now you have a great foundation in formatting spreadsheets!-If you’re interested to learn the core of becoming an Excel power-user (including a whole module covering basic through advanced formatting), take a look at my video-based course on Udemy.Become an Excel Power User in 2.5 Hours | UdemyHope this helps!

David Colver is correct: worksheet Excel can use multiple cores, but VBA uses only one. This is by design (to keep all Excel processes synchronous), and would require a considerable rewrite of VBA APIs to change. The new JavaScript macro language uses asynchronous processing, and potentially can thereby take advantage of multiple cores—but runs much slower than VBA at this point.If macro speed is an issue, you can get considerable improvement by approaching the code correctly. When working on other people’s code, I usually see ten-fold improvement in speed, and it is not unusual to see a hundred-fold or thousand-fold improvement.I have answered over 10,000 forum questions on Excel, and expect code to run with barely a blink in most cases. I have never once wanted more than a single core to work on my VBA code. And lest you think I have a super-fast computer, I use a 2008 Mac laptop running Windows on a virtual machine. Your computer is guaranteed to run VBA code faster than mine.The standard (and relatively easy) techniques for improving VBA speed include:Never use statements that Select or Activate a cell or worksheet. I know the macro recorder does this, but that approach is extremely slow. Learn instead how to use loops in VBA that act on a range of cells or values.If you record a macro, many times the macro recorder captures the state of each property associated with that object. Almost always, you can eliminate 90% of those statements and concentrate on the one or two that actually do what you want. Good examples of this are recorded macros for changing print page layout, cell borders, or chart features. Needless to say, the code runs faster if you don’t need to set properties to their default value.Turn screen updating off while the macro runs. Do not turn it back on at the end of your code (Excel will do this for you automatically).Turn calculation off (manual calculation) at beginning of macro, and change back to automatic calculation at the end of the macroTurn events off at the beginning of the macro, then back on at the endIf you have a chain of macros, one calling another, make the settings for screen updating, calculation and events in the very first macro only. Don’t let each macro in the chain independently control those features.Use array transfer to get data from the worksheet into VBA, and then again to return it back to the worksheet. This is enormously faster than looping through a range changing one cell at a time.Use the .End(xlUp) method to find the last row with data in a range. Then restrict the macro to working only between your starting cell and the last cell found by .End. Definitely don’t work on every cell in a column.Use native worksheet techniques where possible, such as sorting and worksheet functions.Avoid the use of volatile functions (such as OFFSET, INDIRECT and RAND) when you build worksheet formulas. These recalculate whenever a change is made to a value in any open workbook. If your workbook has thousands of cells using formulas with volatile functions, there will be a noticeable delay while the workbook recalculates. Build OFFSET formulas with INDEX instead. And use VBA code to build cell formulas without INDIRECT or RAND.The first VBA code I wrote was in essence to count the number of snowflakes that fit in a bucket, with one machine making snowflakes and another one melting them. My first attempt took over five minutes to get results for an extremely simple problem. Using array transfer to get data into VBA and return the results back to the worksheet improved the speed so I got results in under a minute. My calculations were iterative (trial and error), and I found that improving the algorithm I used to generate guesses improved the speed so results were returned in a couple of seconds. Overall improvement: 150X.I consulted on a project to determine the deflection of a boom crane as it picked up various weight loads. My client had spent many months writing and improving his code. He had already applied the easy suggestions improvements suggested above, but it was still taking almost 20 minutes for each load & boom angle combination. My engagement was for ten hours of consulting time. The client’s code used several trial and error calculation loops (some nested), so I benchmarked the time that each one needed to converge. Using those timings, I identified which pieces of code offered the most potential optimization benefits. Simply by changing the way the next guess was determined allowed me to reduce calc time to 90 seconds. And using the 10 or so actual flex points instead of 500 evenly spaced points reduced the time to about 1 second. Overall improvement: 1300X.A third project I consulted on involved a Monte Carlo simulation of highway pavement under road traffic from cars and semitrailers. The client complained that the VBA calcs were so intense that they took up to an hour to complete per test case. Worse yet, many times his computer would overheat and shut down before calcs could be finished. He needed to send a final report to his customer a few days later, and was in a panic. I had a few free hours for an interesting project. I found nested loops that returned values to the worksheet while each test case converged. By keeping those results in VBA until the test case converged, and improving the next guess for a trial and error section of code, I reduced the recalc time substantially. That improvement allowed my client to complete all his test cases over the July 4 weekend and ship his final report to the customer on time. And his computer never once overheated. Overall improvement: 13X.Bottom line: I am confident that a different approach to your VBA code will eliminate any need to allocate more computing power to Excel macros.Addendum: It is possible to increase the priority that Excel receives in Windows 10. To do so:CTRL + ALT + DEL to launch the Task ManagerGo to the Details tabRightclick EXCEL.EXE and choose Set priority > from the resulting dialogOn my computer, Excel has a priority of Normal. The highest priority is Realtime, three levels above Normal.I wouldn’t expect an earth-shattering improvement in macro speed if you change the priority. Windows tries to adapt to the situation—so Normal might already be the lion’s share of CPU in a computationally intensive macro. And if you do increase the priority all the way to Realtime, be prepared to pull the plug (or battery) on your computer to get it to stop an infinite loop.