1.3 EXCEL to New Heights PACE April 2017 Chad Carter And Bonnie - - PowerPoint PPT Presentation

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
• r 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
• f 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

• r 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

• riginal 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??