In honor of today’s ghoulish occasion, we wanted to provide other worldly insight into a few not-so-scary Excel tricks and tips that will exorcise your Excel formatting demons.
Tricky Issue #1: Rolling a 12-Month Income Statement with Zero Suppression
Did you know that you can make a static 12-month income statement (IS) a rolling IS using Excel and BizInsight? By working with a rolling 12-month IS, you can actually update an entire report in a single cell! Now how sweet is that?
Step 1: Set cell Q6 = B3 (the current period you will change in your parameter box). For the other periods, starting in cell P6, use the formula =IF(Q$6-1=0,12,Q$6-1).
If the current period minus 1 is 0, then change the cell to period 12.
Otherwise take the current period minus 1.
Step 2: Set Q8 = B2 (the current year in your parameter box). For the other years, starting in cell P8, use the formula =IF(P$6=12,Q$8-1,Q$8).
If the period is 12, then do the current year minus 1.
Otherwise take the current year.
Step 3: Make sure the year and period in the function argument box point to the year and period in the same column. For example, all the functions in column Q would have the following year and period (NOTE: Excel anchoring is very important in this report):
Step 4: Use the formula =IF(AND(MIN(F12:R12)=0,MAX(F12:R12)=0),0,1) to suppress the rows that have only zero amounts.
Fill in the range for the MIN and MAX for the rows that you want the formula to check for zero rows. In this example, columns F:R is the range that should be input in the above formula.
*Best Practice: Hard code a 1 in the rows that you do not want suppressed. Hardcoding a 1 in rows that you do not want suppressed will ensure that these rows will never be hidden while using the zero row suppression filter
Step 5: Apply an Excel filter to the column you put your zero row suppression formula in. In this example, the filter is in column S.
Step 6: Uncheck the 0 and those rows will be suppressed.
Step 7: Change the font color in column S to white. This will allow the column to remain hidden for report design but will keep the column visible to reapply the filter.
Tricky Issue #2: Saving Formulas to the Navigation Pane
Hate having to constantly enter the same formulas for multiple reports every month? You can actually save and add Excel formulas to the navigation pane to drag and drop into a workbook.
Step 1: Right click on BizInsight and click insert. (A new node will now appear at the bottom.)
Step 2: Right click on the “new node” and click properties so a box pops up with different fields containing information about the item created.
Step 3: In the description field type in “helpful excel formulas.” This is the description that will show up on the BizInsight navigation pane.
Step 4: Right click on the helpful excel formulas node and click insert. (A new node will appear under the “helpful excel formulas” node.)
Step 5: Right click on the “new node” and click properties so a box pops up with different fields containing information about the item created.
Step 6: In the formula type in the zero suppress formula =IF(AND(MIN()=0,MAX()=0),0,1) and type in zero row suppression for the description.
Remove the range after the MIN and MAX as that will vary depending on the report it is used in. (When using this function on a report, drag and drop in the spreadsheet where it is needed and select the range after MIN and MAX in the formula.)
Step 7: Repeat this process for additional formulas to be added to the navigation pane and feel free to nudge the formulas up or down accordingly.
To wrap up this tricky issue, just click the “save changes to BizInsight” button.
Tricky Issue #3: Easily Switching Between Functions
Ever wanted to switch between functions automatically instead of having to manually change multiple fields? This tricky issue is actually quite easy to fix!
Step 1: Copy the current period column (in this example, cells E9:E42) and paste in column F.
Step 2: Select column F and use Ctrl+H to find and replace $B$2, with $B$2-1.
This replaces the cell reference to the year with the year-1, allowing you to return the prior year data quickly and easily.
Make sure to double check that the find and replace was only applied to the column desired. Using a single cell for current and prior years allows you to change a single cell that updates current and prior year data at the same time.
Step 3: Copy cell range E9:F42 and paste in the current YTD (cell I9).
Step 4: Select the range I9:I42. Use Ctrl+H to find and replace MTDNET_GL with YTDNET_GL.
This allows you to easily switch between monthly and yearly functions without having to drag over an additional function from the navigation pane and fill out the necessary parameters.
Tricky Issue #4: Altering Analysis Set List Structure
Unfortunately, analysis set lists are dynamic and, therefore, they revert back to their original form unless altered. By changing the data range properties, the list will keep the desired form while pulling in new data when refreshed.
Step 1: Pull over the analysis set list that you want to work with (in this example AR Customer List).
Step 2: Delete the columns that you do not want to see (H:L).
Step 3: Right click in the analysis set and then choose data range properties.
Step 4: Check preserve column sort/filter/layout.
Now when the analysis set list is refreshed, the new format will stick!
Tricky Issue #5: Clearing Cache
Sometimes when working in Excel constantly and dragging over a lot of data sets into the program, the data caches can become full. All that’s needed to fix this issue is to clear the BizNet data caches. Easy as 1, 2, 3!
Step 1: Click on the function editor (fx).
Step 2: In the “select a category” box, scroll down to BizInsightXL.Foundation and click OK.
Step 3: Now select ClearCaches and click OK.
This year, don’t be consumed by the terror of formatting. Don’t be afraid of those ghosts and fulfill your reporting goals! We hope you find these tips as tasty as candy corn and they help with your daily Excel usage. Have a very Happy Halloween!
About BizNet Software
BizNet Software is an award-winning company and market leader for Microsoft Excel in-memory, cloud-enabled reporting and business intelligence. BizNet is endorsed by over 200 partners, 15 software publishers, and currently has over 20,000 users in 2,100 companies in 30 different countries. Find out more at www.biznetsoftware.com.
The post 5 Tricky Excel Issues Turned Treat appeared first on BizNet Software.