It covers:
Applying conditional formatting to highlight key performance thresholds.
Formatting percentages and values for consistent presentation.
Building a classification-based structure for row calculations (e.g., net profit, margin).
Using pivot table features to analyze data across months and years.
Setting up page filters for focused analysis and dynamic interaction.
Ensuring all styling and formatting update automatically as data changes.
Key Steps
1. Set Up Conditional Formatting 0:00
-
Apply conditional formatting to the income statement:
-
Values above 5% should be highlighted in green.
-
Values below 5% should be highlighted in red.
-
2. Format Percentages and Values 0:26
-
Style the percentages:
-
Use one decimal place and add a percentage sign for ratios (e.g., net profit percent).
-
Ensure no decimal places are used for other values.
-
3. Apply General Styling Rules 1:02
-
Bold key figures such as net sales and net profit.
-
Add borders:
-
Use a top single border for net income.
-
Use a double underline for additional emphasis.
-
4. Create an Interactive Report 1:14
-
Build the report using a classification structure that allows for row-based calculations (e.g., net profit, net profit percent).
5. Pivot Data for Dynamic Analysis 1:36
-
Move items around in the pivot report:
-
Place 'month' in rows and 'income statement' in columns to analyze by year and month.
-
6. Adjust Styling Dynamically 2:02
-
Ensure that styling is retained during pivoting:
-
Maintain one decimal place for percentages.
-
Ensure whole numbers are displayed correctly.
-
7. Use Page Filters for Specific Analysis 2:39
-
Set up page filters to allow single selection:
-
Change filter type to single selection for focused analysis (e.g., net sales).
-
8. Analyze Variances and Percentages 3:30
-
Review variances in the report:
-
Ensure color coding reflects changes in data.
-
Maintain consistent styling for percentages.
-
9. Finalize Report Dynamics 4:06
-
Confirm that the report is dynamic and responsive:
-
Ensure all styling and conditional formatting updates correctly with data changes.
-
Cautionary Notes
-
Ensure that all formulas and calculations are accurate before finalizing the report.
-
Regularly check for updates in data to maintain report accuracy.
Tips for Efficiency
-
Familiarize yourself with pivot table functionalities to enhance report interactivity.
-
Use templates for styling to save time on formatting in future reports.