Which Excel feature to explore when you’re at the very beginning? — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily

Which Excel feature to explore when you’re at the very beginning?

by on
in Admin Pro Forum

Question: "I know only the very basics of Excel—data entry, sorting and the most basic formulas, so it's not much more to me than a calculator. If I want to take a tiny step forward in it and learn something snazzy but actually useful, what would be a good (and simple) thing to attack?" - Beth, Receptionist

See comments below, and send your own question to Admin-Pro@nibm.net.

{ 9 comments… read them below or add one }

jabbmabb March 13, 2014 at 7:25 pm

I would suggest learning how to create graphs/charts. Being able to visually present information is a good thing. You might also think about the types of documents you are creating or working on. How could you repurpose them? Can they be modified to have the same results, but done in a different way? If you have time to play around, start learning the function keys and what they do. Start learning the various Formulas, e.g., Count, CountIf, CountA, etc., and then see how those apply to the spreadsheets you work on. Or look for a need in the office and develop spreadsheets that fill the need, using graphs and as others have suggested, Pivot Tables, etc.

Reply

Lisa Brouard January 15, 2014 at 3:00 pm

Since you’re new, I recommend creating and saving a workbook that is solely used for practice/testing. You can start by creating simple lists in it, then playing with the data to learn sorting, formatting, numbering, etc.

For instance, I recently created a master employee list in Excel with fields for name, birthday, start date, contact phone numbers, etc.

I need to track how many employees are in a particular department so I used the first column to number the employees. Instead of individually typing in the number for 150 employees, I used the “Auto Fill” feature to populate the rest of the numbers. I filled the first cell with the number 1 then proceeded to fill the series. The Auto Fill feature can be found by putting your mouse cursor in the bottom right corner of a cell until it creates a + symbol. Left click when you see the + and drag downward to fill the other cells automatically. If it just copies the same number down the line (in my case every cell said it was 1), just click on the little box that pops up to the bottom right of the fill area. There you can choose to fill the series. You can also use this nifty feature to fill the days of the week, months, years, etc.

When you are comfortable with this, you can learn to link the data from one worksheet to another. For instance, I made a contact list by creating a new worksheet in the same file with the master list. Instead of retyping the employee names, office numbers and cell numbers, I linked to the information on the master sheet and had Excel pull the names and numbers over to the new sheet. Now, if an employee changes cell numbers, I only need to change it once on the master list as the other lists (like my contact list) pull their info from the master. This can be a huge timesaver, not to mention the fact that all my lists are saved in one spot and in one file – no more searching for file after file.

Finally, if I don’t know how to do something, I Google it. If you type what you want (like “Excel 2010 how to link cells between worksheets”) in Google, the answer pops up. There are loads of answers out there.

Good luck!

Reply

Karen January 13, 2014 at 1:02 pm

Conditional formatting’s kind of fun. You can set up a range of cells to be a different color, highlight, bold, etc., based the value in each cell. For instance, you could have all cells that have a negative balance (less than zero) have a red font and highlighted in yellow. Those that do not meet the conditions will remain at your default font.

Reply

Frances January 10, 2014 at 8:05 am

I have been using Excel for so long, I don’t even remember what I started with. As an admin, I use it daily to track costs, keep up with mailing lists and various other tasks. Excel is great for keeping information that you have to manipulate many different ways – i.e. sales figures by salesperson, by location, by product line, etc. Once you decide what you need, pivot tables can really make Excel your best friend. With just a few button clicks, you can have a complete report that will impress the boss and only you know how simple it was! Good luck and happy computing!!

Reply

JoAnn Paules January 10, 2014 at 6:23 am

Pivot tables are great and so are the lookup formulas. Most of what you should focus on next depends on the type of data you work with.

Head on over to http://www.contextures.com, a site run by Deb Dalgleish, a Microsoft MVP in Excel. Her instructs are very easy to understand and I love her video how-tos.

Reply

Theresa Kasel January 9, 2014 at 6:52 pm

As mentioned before, Pivot Tables are a good thing to learn. I would also learn to do V-lookups and H-lookups.

I often use Excel spreadsheets to hold data for mail merges (especially mailing lists) — so learn how to set up a table of data and use it as the merge file for Word. (HINT: Break data down to its smaller component — have a separate cell for city, state and ZIP, don’t put it all in one cell.)

Graphs are pretty simple and you will want to learn those as well.

Reply

V. January 9, 2014 at 5:55 pm

If you use repetitive information such as a salesman name or city/state. Learn how to set up a DataValidation List. Note: Always remember to place the “list” somewhere easily hidden and not easily deleted.

Reply

liz January 9, 2014 at 5:36 pm

I agree, Pivot Tables are the place to start. There are free online sources for tutorials. You can always play around with them by dragging and dropping the field names. Be sure to go to the Pivot Table Tools Design tab and try out the different report layout options and style options.

I would also suggest a few keyboard and mouse shortcuts. Highlight a group of populated cells by holding Shift and Control and the arrow key for the direction you want to go. Copy down or across by clicking and dragging the little square in the bottom right corner of the cell.Or, double click that little square to copy down the entire length of the populated cells in the column left of the cell you are in. If you right click the bottom bar of Excel (below the tabs) you can have Excel display things like the sum, count, max, and average of a highlighted group of cells. Just select the options you want to see and they will appear in that bottom bar.

Reply

Victoria January 9, 2014 at 4:29 pm

I would say start with Pivot Tables. They can be very basic to very advanced. You may not think to use Excel for this, but I use Pivot Tables to “audit” meeting acceptance from Outlook. Sometimes for a large meeting, I need to show my boss who has Accepted, Tentative or Declined and would like it grouped as such. To do this, I use the Copy Status to Clipboard feature on the meeting invite and past it into Excel. I can then create a Pivot Table to group by status. You can even add a bit more info such as department to the Excel data so you can be sure at least one person from each department has accepted.

Reply

Leave a Comment