Shrink a worksheet to fit on one page
- Click Page Layout.
- Select the Page tab in the Page Setup dialog box.
- Select Fit to under Scaling.
- To fit your document to print on one page, choose 1 page(s) wide by 1 tall in the Fit to boxes.
- Press OK at the bottom of the Page Setup dialog box.
In the Navigation Pane, click the table or query on which you want to base the report. On the Create tab, in the Reports group, click Report. Access builds the report and displays it in Layout view. For more about viewing and printing your report, see the section View, print, or send your report as an e-mail message.
Creating a Scenario Summary Report in Excel 2007
- 1Open the workbook that contains the scenarios you want to summarize.
- 2Choose What-If Analysis→Scenario Manager in the Data Tools group of the Data tab.
- 3Click the Summary button.
- 4Click OK to generate the report.
Manually create a PivotTable
- Click a cell in the source data or table range.
- Go to Insert > Recommended PivotTable.
- Excel analyzes your data and presents you with several options, like in this example using the household expense data.
- Select the PivotTable that looks best to you and press OK.
Click the chart, and then click the Chart Design tab. Click Add Chart Element and select Data Labels, and then select a location for the data label option. Note: The options will differ depending on your chart type. If you want to show your data label inside a text bubble shape, click Data Callout.
Follow these steps:
- Click File > Info > Check for Issues.
- Choose Check Compatibility.
- To check for compatibility automatically from now on, check the Check compatibility when saving this workbook box. Tip: You can also specify the versions of Excel that you want to include when you check for compatibility.
Subtract your total expenses from your total sales revenue to calculate your quarterly earnings.
Enter Your Data
- Open Excel to begin working on a new sheet.
- Type the title of your report in cell A1.
- Type in the labels for your four quarters.
- Enter your sales data in cells B2, C2, D2 and E2 below each quarter for which you are reporting.
- Enter the formula "=sum(B2:E2)" in cell F2 to calculate the total sales.
To create a Scenario Summary:
- On the Ribbon's Data tab, click What-If Analysis.
- Click the drop down arrow, and click Scenario Manager.
- Click the Summary button.
- In the Scenario Summary dialog box, for Report type, select Scenario Summary.
- Press the Tab key, to move to the Result cells box.
- On the worksheet, click on cell B6.
If you're using
Excel 2013, use the new Quick Analysis button.
Next, create the PivotTable report:
- Highlight your data table.
- From the Insert ribbon, click the PivotTable button.
- On the far right, select fields that you would like on the left-hand side of the report and drag them to the Rows box.
Try it!
- Select a cell within your data.
- Select Home > Format as Table.
- Choose a style for your table.
- In the Format as Table dialog box, set your cell range.
- Mark if your table has headers.
- Select OK.
A pivot table is a table of statistics that summarizes the data of a more extensive table (such as from a database, spreadsheet, or business intelligence program). Pivot tables are a technique in data processing. They arrange and rearrange (or "pivot") statistics in order to draw attention to useful information.
If your pivot table is the traditional type (not in the data model), grouping problems are usually caused by invalid data in the field that you're trying to group. a blank cell in a date/number field, or. a text entry in a date/number field.
The Simple Rule for Grouping Dates in Pivot TablesAll cells in the date field (column) of the source data must contain dates (or blanks). If there are any cells in the date field of the source data that contain text or errors, then the group feature will NOT work.
On the Analyze tab, click Group Field in the Group option. When your field contains date information, the date version of the Grouping dialog box appears. By default, the Months option is selected. You have choices to group by Seconds, Minutes, Hours, Days, Months, Quarters, and Years.
Example of How to Group in Excel
- Select the rows you wish to add grouping to (entire rows, not just individual cells)
- Go to the Data Ribbon.
- Select Group.
- Select Group again.
To group the numbers
- Right-click on one of the unit numbers in the pivot table.
- In the popup menu, click Group.
- In the Grouping dialog box, enter 1 in the Starting At box.
- In this example, the highest number of units is 50, and you can type a higher number, if necessary.
- Click OK, to apply the grouping.
Option 1: If you don't care how Excel formats your datesNext right-click one of the date row labels in the PivotTable > select Field Settings > Layout & Print tab > check the 'Show items with no data' box. From the Months filter select the months you want to display in your PivotTable – for me this is March.
To calculate excel date quarter for a calendar year:-
- Select the cell B5 and write the formula to calculate the quarter number.
- ="Quarter"& INT ((MONTH (A5)-1)/3) +1&" "&YEAR (A5) and press Enter on the keyboard.
- This function will return the Quarter no.
- To return the quarter no.
Except the above formula, you can also apply this formula: =TEXT(A2, "mmm") & "-" & TEXT(A2, "yyyy"). 2. In above formulas, A2 indicates the date cell that you want to use, and the separator “-” is used to separate the month and year, you can change it to any other delimiters you need.
