Here at Source One we like to keep our analytic skills sharp. One way we do that is by sending Excel tips out to our staff a couple times a month. This serves a couple purposes: people have the opportunity learn something new, or refresh their memory for seldom-used skills that might slip the mind. Because it's in their email, they can review these tips when they have the time.
Here are some recent tips we shared with our staff. Hopefully they can help you too.
1) Multi-tier chart Axis:
You can create charts with multiple tiers of data on the X-axis as shown here.
To do this, format the source data as shown in the following table, allowing for blank spaces in the first and second tiers.
Bonus tip: Instant Chart
Select any cell within your data set and press F11. Excel will create an instant chart based on the current data, which you are then free to customize as normal.
2) Editing Multiple Sheets Simultaneously
Often there is a need to set up several sheets in a similar manner, or to make the same edit to identical sheets in a workbook. In the same way that you are able to select multiple cells in a worksheet, you can also select multiple sheets within a workbook. Changes that you make will then apply to all selected sheets.
1) Click the first sheet you want to work with
2) Hold shift and click the last sheet you want to work with. The selected sheets will be highlighted in white.
Any changes you make will populate on all the selected sheets.
- Make sure you only have a single sheet selected when entering data specific to that sheet. Do this by right-clicking the sheet and selecting "Ungroup Sheets" or by clicking on a sheet that's not selected. Failure to do this will result in identical data across all sheets in the workbook
- Certain features, like advanced formatting and inserting text boxes, are disabled while multiple sheets are selected. Other features, like re-sizing columns, will only apply to the visible sheet and will not populate through to other sheets.