1.3 EXCEL to New Heights PACE April 2017 Chad Carter And Bonnie Chisholm
Why should I use EXCEL? Excel is a great resource for keeping lists of all kinds. • Microsoft Excel is a program that provides worksheets • comprised of rows and columns. Text or numeric information/data can be stored in the workbook • similarly to a Microsoft Word table, but the power of Excel is its ability to perform simple to complicated mathematical calculations and to sort and filter data, among other things. To open, Excel, click on the Excel icon on your computer:
Inserting Rows/Columns and Hiding/Unhiding Determine where you want to enter an additional row/column, select the • column/row then right click on your mouse. This will enter only one row or column. To insert multiple columns/rows, select (highlight) the number of columns • or rows you wish to add, then right click on your mouse and choose INSERT. Sometimes it is useful to hide information on a spreadsheet – the data • does NOT go away but is simply not visible in that view. Select the columns/rows to hide and then right click on your mouse and choose HIDE. To reverse this, do the above steps and select UNHIDE. A tip: Avoid hiding Column A or row 1. –
Finding Blank Rows and Columns Sometimes , you’ll find your table data separated by blank rows or columns. Luckily, cleaning up this problem is quick and easy. Simply select a column and hot F5. From there, you can choose Special and then Blanks. Once these blank cells are zeroed in on, you can go to the Home tab and choose Delete.
Wrap Text Home Tab, then “Format” (over toward the right and top of the screen) – • Then “Format Cells” – (at the bottom of the drop down list) • Then “Alignment Tab” – (across the top of the box) • Then click the “Wrap Text” box (under Text Control) • • This will allow for whatever text you enter to automatically go to the next • line, regardless of how large or small your cell is. This is a useful function if you have a lot of text to enter into a specific cell. • Remember to click OK. •
Sorting & Filtering Excel is an amazing tool for analyzing data. And Sort and Filter are some of • the most commonly used features to help you. To change the order of your data, you’ll want to sort it. To focus on a specific set of your data, you can filter a range of cells or a table. To sort, click on the column to be sorted, then click on the “Sort and Filter” • function under the HOME tab and toward the top right of the screen. (The icon is a funnel with the letters “A” and “Z”). Select from the dropdown box the sorting method needed. Options even lets you sort left to right! To filter, select the data you want to filter (for best results columns should • have headings). Click DATA, then Filter icon. You will notice a little down arrow in a box appears in the column header. To remove filter, select column and then click on Filter icon again and you should see the down arrow in the box disappear from the column header.
Filtering • You can copy filtered data to another part of the sheet or to a new sheet
• Sorting and Filtering:
Auto Fill • Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells.
Auto Sum Auto Sum is another widely used function. To add all the numbers in a • column, highlight all the cells containing numbers in a column, then click the FORMULAS tab, then click Auto Sum. The total will appear in the next cell at the bottom of the column.
Freeze Panes Freeze Panes is a function that is helpful when worksheets have many rows • of data. The first row of a sheet often contains headings such as Name, Address, Phone Number, etc. To keep this row in place as you are entering or scrolling through multiple rows of data, click on the VIEW tab, then click “Freeze Panes” and select the best -suited option. The top row with the headings will always be visible a once Freeze Panes option is selected.
Find and Select • Use to find certain text • Use to find and replace certain text
Find or Filter: WildCards
Charts & Graphs Select data Press F11 or use INSERT options
Text to Columns • This features will split a column of data. For example, you need to separate first and last names (or part names and numbers, or any other data) into separate columns. • A new feature in EXCEL 2013 is Flash Fill and this gives a similar result
Transpose • Changing rows to columns; columns to rows • Pick a spot in table with enough room to paste your data because it overwrites any data that is there • Can’t transpose data in an EXCEL table; must convert table to a range first
CONCATENATE Function • Use CONCATENATE to join two or more text strings into one string. • Another way is to use & (=Cell& “ ” &Cell)
COUNTIF • =COUNTIF(L2:L15, “YES”)
Remove Duplicates • Located under DATA tab. • When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.
Validate Data to Make Drop-Downs • 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.
Validate Data to Make Drop-Downs • 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, you could 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.
Mail Merge • Mail Merge Wizard in WORD uses an EXCEL spreadsheet to allow you to customize letters, labels, email messages
Continuity Folder
Continuity Folder • And here’s…………. Bonnie
Questions?? • Other ideas/concepts that you find helpful in EXCEL??
Recommend
More recommend
