A new feature in Excel 2013 called Flash Fill allows the user to separate or combine data within entire columns based on the pattern that Excel picks up from the other columns. You can also use Flash Fill to change the case of text. As long as Excel can pick up a pattern, it will work to complete the column for you. (more…)
Do you find yourself forgetting keyboard shortcuts for your Excel macros? When first recording a macro, you will have an opportunity in the Record Macro dialog box to specify a shortcut key for the macro. However, you may eventually forget the shortcut, or if you didn’t assign a shortcut key when you first recorded the macro, you may now like to assign one – or perhaps you have even decided to change the shortcut key to something different.
To view your macro shortcut keys, on the View tab of the Ribbon, click the Macros button, select your macro, and choose Options…. You will see the Macro Options dialog box:
This dialog box shows the shortcut key.
Sometimes a column heading in Excel seems about a mile wider than any of the entries below it in the column. In the graphic below, the Column D heading, Years With Company is 18 characters wide while all the entries below it will be only one or two characters. In many such cases the goal is to keep the column as narrow as possible. Let’s look at some techniques for positioning the text – using word wrap and alignment.
The text in cell D1 is much wider than that in other cells in column D
On this week’s episode we’re talking about creating Pie Charts in Excel and discuss semiotics in different cultures.
And don’t forget to join us for the IT Training Conference!
The IT Training Conference is back this year, after the Statewide IT Conference. All of the sessions will be held on September 25th starting at 1pm in the Indiana Memorial Union. Enrollments are underway, and classes are filling up fast.
Check our conference page for a full schedule and instructions on how to sign up and listen to our special conference episode for all the details.
You may need to save Excel data in text files in order to be able to use the data in other programs or applications. There are several text file formats that are supported by Excel. The two commonly used file formats are: delimited text files (.txt) in which tabs separate each field of text, or comma delimited files (.csv) in which commas separate each field. You can easily save Excel data into one of these formats or import these text formats into Excel. Remember, however, that formatting, graphics, and other objects will be lost when saving data in a text file.
We will illustrate how to save Excel columns as a comma delimited file. The original data might look something like this:
0000011111 Bill
0000022222 Susie
0000033333 John
0000044444 Mary
NOTE: To display preceding zeros in a column, you can go to the Format Cells dialog box and choose Custom. On the right-hand side, scroll up (if necessary) and click the single 0. In the Type field, enter as many zeros as required digits (in this example, the number of zeros would equal 10).
On this week’s episode we’re talking about what’s new on the IT Training Tips blog.
On this week’s episode we’re talking about how to format numbers to include leading zeros in Excel.
When you import large data sets into Excel, some columns of text may not display in the case you would prefer. For example, you could have a column of names that are all upper case where you want only the first letter of the name to be upper case. Or you might have a list of model IDs that are lower case where you expected upper case. There are three functions for working with text that might be just the tools you need to improve the appearance of your data.
The functions are UPPER, LOWER and PROPER. This function, =UPPER(A1) will convert the text in cell A1 to all upper case. Similarly, the function =LOWER(A1) converts all the text in cell A1 to lower case. The PROPER function converts just the first letter of the text in the designated cell to upper case.
The best way to convert a column of data is to:
1) Back up the spreadsheet
2) Insert a blank column to the right of the column holding the text you would like to change.
3) In the first row of the new column place the function you need and referencing the cell where the text sits assuming here that my original text is in cell A1: =PROPER(A1)
Copy the function down the rest of the column
We’re back after a little break. Jason’s talking about Pivot Tables and letting you know how to use them, and describing which IT Training workshops contain more information. We’re going to be moving to an every other week schedule from now on, so keep an eye out for our new episode in 2 weeks!
