I admit it, I'm a geek. The good news is that I can do really cool stuff sometimes on my computer.
One of a geek's best friends is Excel spreadsheets. Have you ever used some of these really cool features?
Cell formats - on the Home tab
You can use the buttons on the home tab to set the format of a cell, row or column so that it displays nicely. For example, you can select $ format, then add or remove decimal places if you do (or don't) need to see the amounts less than a dollar. You can also choose from several date formats which may or may not display the year, or may use the name of the month rather than just a numeric value.
Merge & Center - on the Home tab
If you have values that are too wide for a single cell, you can merge two cells together to give it more space. The fastest way to do this is to use the "merge and center" button, which looks like this <-a-> in a box (in alignment section).
Paste Format- on the Home tab (icon looks like a broom)
If you're typing and the fonts, size, colors or other formatting are not consistent, you can highlight a cell, row or column that has the format you'd like to duplicate. Then click on the broom icon, then click on the cell, row or column where you'd like the format to be applied. This is much faster than applying the formats one at a time (font color, font size, centering, etc.) on a cell or area. This icon is available in all of the Microsoft Office packages (Word, Excel, etc.).
Paste Special - on the menu when you right click
If you are copying values from one cell to another, or from a totally different document, you can use "paste special" to avoid using the format of the source document. Or you can also use "paste special" to copy just the value (or answer) of a calculation, rather than the calculation itself.
Data sorting - on Data tab
When you have a large spreadsheet it's sometimes useful to sort it by one of the column values. For example, if you have a list of all the sales for your company, it might be useful to sort the list by date, to see the most recent sales, or sort by customer to see trends in what your customers want. Once your data is sorted, you can better use the functions like subtotals, filters and charts/graphs.
Subtotals - on Data tab
You'll need to sort your data first, then use subtotals to count records or sum values in the sorted data. This is really helpful for analysis. How much revenue did you earn for each of your products? How many people on your mailing list live in zip code 33837?
Autofilter - on Data tab (icon looks like a funnel)
If you have a lot of rows of data, you may want to look at just a subset of your data sometimes. You can use the filter button to decide what criteria to use for your filter (column A, values = 0 for example). You can toggle the filter on and off using the button with the funnel. You can select the value of any cell in the column as the value to use for filtering. You can also use custom values or basics such as blank, non blank, or boolean values like greater than, less than, not equal, and others.
Print Titles - on Page Layout tab
You don't need to copy the header row in your spreadsheet so that it appears in your data where each of your page breaks appear. Just insert your column names in row 1. Then you can use the "rows to repeat at top" option in the "print titles" section to have the first row or set of rows print at the top of every page. This allows the page breaks to fall wherever they have to and it doesn't require that you move your title rows to match the page breaks. That is a lot of extra work that you can avoid.
Header/Footer - on Insert tab
You can also use headers and footers to get a consistent title or page numbers at the top or bottom. Use Excel's buttons to get automatic page numbers, dates, and other values.
I hope you find these useful next time you're using Excel. If you'd like to hear about more useful functions in Excel, please post a comment.
No comments:
Post a Comment