If you have definitely gone through the 1st part of "Tips To Become Excel Pro", I welcome you to the Next Part of Becoming Pro in Excel.
Use Graphics in Charts
You can put a graphic in any element of an Excel chart. Any element. Each bar, piece of pie, etc., can support its own image. For example, above, there's a South Dakota state flag on the pie chart (placed by selecting the slice, using the Series Options fly-out menu, and selecting "Picture or texture fill"), plus an embedded PCMag logo (placed with the Insert tab's Pictures button). You can even go with "no fill" at all, which caused that missing slice.
Clip art can be cut and pasted to an element—dollar bills to show dollars spent, water drips for plumbing costs, that kind of thing. Mixing and matching too many graphical elements makes it impossible to read, but the options you have are worth some digital tinkering. Let your resident graphic designer check them out before you use them.
Save Charts as Templates
Excel has more types of charts than Jimmy Carter's got peanuts, but it's almost impossible to find a default chart perfect for your presentation. Thankfully, Excel's ability to customize all graphs is exemplary. But when you have to recreate one, that's a pain. It doesn't have to be. Save your original chart as a template.
Once a chart is perfected, right-click on it. Select Save as Template. Save a file with a CRTX extension in your default Microsoft Excel Templates folder. Once done, applying the template is cake. Select the data you want to chart, go to the Insert tab, click Recommended Charts, and then the All Charts tab, and the Templates folder. In the My Templates box, pick the one to apply, then click OK.
Some elements, like the actual text in the legends and titles, won't translate unless they're part of the data selected. You will get all the font and color selections, embedded graphics, even the series options (like a drop shadow or glow around a chart element).
Work With Cells Across Sheets
This one, called 3D Sum, works when you have multiple sheets in a workbook that all have the same basic layout, say quarterly or yearly statements. For example, in cell B3, you always have the dollar amount for the same corresponding week over time.
On a new worksheet in the workbook, go to a cell and type a formula like =sum('Y1:Y10'!B3). That indicates a SUM formula (adding things up) for all the sheets that are titled Y1 to Y10 (so 10 years' worth), and looking at cell B3 in each. The result will be the sum of all 10 years. It's a good way to make a master spreadsheet that refers back to ever-changing data.
Hide in Plain Sight
It's easy to hide a row or column—just select the whole thing by clicking the letter or number header, right-click, and select "Hide." (You can unhide by selecting the columns to either side all at once, right-clicking, and selecting "Unhide"). But what if you have just a little section of inconveniently placed data you want to hide, but you still want to be able to work with? Easy. Highlight the cells, right-click, and choose Format Cells. On the Number tab at the top, go to Category and select "Custom." Type three semicolons (;;;) in the Type: field. Click OK. Now the numbers aren't visible, but you can still use them in formulas.Hide A Whole Sheet
Your typical Excel workbook—the file you're working in—can get loaded with plenty of worksheets (each sheet indicated by a tab at the bottom, which you can name). Hide a sheet if you want, rather than delete it, making its data still available not only for reference, but also available to formulas on other sheets in the workbook. Right-click the bottom sheet tab and select Hide. When you need to find it again, you have to go the View tab at top, click Unhide, and pick the sheet name from the list that pops up.Use Personal Workbook for Macros
When you do unhide an entire workbook, you'll probably see a workbook listed you didn't know you hid: the Personal.XLSB file. This is actually the personal workbook Excel created for you; it's opened as a hidden workbook every time Excel starts. The reason to use it? Macros.Pivot! Pivot!
Whole books have been devoted to PivotTabels. They're summaries of your giant collection of data that makes it much easier to parse the info based on your reference points. For example, if you've got the entire set of grades for all your students across all tests for the whole year, a PivotTable can help you narrow things down to one student for one month. It behooves anyone with big data to play with them (make a copy of the original data to play with first).To create one, check that all the columns and rows are titled the way they should be, and then select PivotTable on the Insert tab. Better yet, try the Recommended PivotTables option to see if Excel can pick the right kind for you. Or try the PivotChart, which creates a PivotTable with an included graph to make it easier to understand.
Conditionally Format
Looking at a huge amount of data and wondering where the highlights are? Who has the highest (or lowest) score, what the top five are, etc.? Excel's Conditional Formatting will do everything from put a border around the highlights to color coding the entire table. It'll even build a graph into each cell so you can visualize the top and bottom of the range of numbers at a glance. (Above, the highest numbers are in speedy green, the lowest in halting red, with a spectrum in between.) Use the Highlighted Cells Rules sub-menu to create more rules to look for things, such as text that contains a certain string of words, recurring dates, duplicate values, etc. There's even a greater than/less than option so you can compare number changes.Validate Data to Make Drop Downs
Creating a spreadsheet for others to use? If you want to create a drop-down menu of selections to use in particular cells (so they can't screw it up!), that's easy. Highlight the cell, go to the Data tab, and click Data Validation. Under "Allow:" select "List." Then in the "Source:" field, type a list, with commas between the options. Or, click the button next to the Source field and go back into the same sheet to select a data series—this is the best way to handle large lists. You can hide that data later, it'll still work. Data Validation is also a good way to restrict data entered—for example, give a date range, and people can't enter any dates before or after what you specify. You can even create the error message they'll see.
No comments:
Post a Comment