Geeks on Campus

Another amazing bgsu blog

Archive for the 'Excel' Category

Filters

Posted by jeggent on 5th September 2017

This is part of the Tuesday Excel Tips series.

Check out the listing of all the Tuesday Excel Tips.

A quick way to organize a data table in Excel is with Filters.  You can use filters to hide the rows of data that do not match the parameters you specify.  These parameters could be lists of values in one or multiple columns or greater-than less-than or ranges for number values.  You can also use filters for sorting.  To get started, select the column headers that you want included in the data table.  Click the Filter icon on the Home or Data menus.  Click the new icon next to the column header to see your options.  Once you have filtered a table you will see the icon change to a filter and the status bar at the bottom will show how many matches there were.  The example below shows “4 of 7 records found”.  You can re-select all of the items or click the filter clear icon to un-filter the list.

Filter

Posted in Excel | 1 Comment »

Removing Duplicates

Posted by jeggent on 29th August 2017

This is part of the Tuesday Excel Tips series.

Check out the listing of all the Tuesday Excel Tips.

You may at times have duplicate values depending on the level of detail in your data.  For example, if you have class enrollments listed, you will have multiple rows for each student within each term.  That is an obvious situation.  But there may be other times where it is not obvious that you would have duplication, such as a student having multiple careers in the same year.  Excel delivers ways to quickly identify duplicate values and eliminate them if needed.

Identify Duplicates

  1. Select a column or group of cells that you wish to identify duplicates among.
  2. Click the Conditional Formatting button on the Home tab.
  3. Select Highlight Cell Rules and click Duplicates Values…
  4. Choose the color you prefer in the box that pops up and click ok.

IdentifyDuplicates

Remove Duplicates

  1. Select column with duplicates values you want to be removed.
  2. Click Remove Duplicates from the Data tab.
  3. You get options to expand the selection and choose other columns or just remove values
    from the selected column. Hit ok when you’ve made your choice

RemoveDuplicates

Posted in Excel | 6 Comments »

Quick Formulas

Posted by jeggent on 22nd August 2017

quickThis is part of Tuesday Excel Tips series.

You probably already know that the real power of Excel is in using formulas.  But you may not know that Excel comes with a few handy formulas already defined to make them easy for you to use.

If you select the cell below or to the right of a series of number, you can use the quick formula button to get the Sum, Average, Count, Max, or Min of the series.  If you just click the Sigma button it will Sum the numbers.  If you click the little down arrow you can select one of the other functions.

You can also do like the example below and select a data series and then use the quick formula.

Check out the listing of all the Tuesday Excel Tips.

 

QuickFormulas

Posted in Excel | 2 Comments »

Quick Info

Posted by jeggent on 15th August 2017

This is part of Tuesday Excel Tips series.

It’s fitting that I only have a small amount of time today to do this post.  Because this tip is all about getting information quickly.  If you highlight/select a group of cell in Excel it will give you some information about the data in those cell in the status area at the bottom of the window.  If the values are numeric Excel will show you the average, count, and sum of the values.  If the values are non-numeric then it will show you a count.  This is very hand if you just need to get one of those figures quickly and don’t need to keep the calculation on the sheet with the rest of the data.

Check out the listing of all the Tuesday Excel Tips.

quickinfo

Posted in Excel | 2 Comments »

Copy Down

Posted by jeggent on 8th August 2017

This is part of the Tuesday Excel Tips series.

If your spreadsheet needs some kind of list or series of labels such as a count, days of the week, days, months, etc. Excel can help you out with that.  As shown in the example below, a count is being entered into the first column.  We only need to enter the first couple of values so that Excel can determine that we increment the value by 1 each time.  Select those values and then place your mouse pointer on the box in the lower right corner of the selection.  Drag the cursor down to the last row you’d like filled and Excel will fill in the values for you.  If you already have data populated in the adjoining rows, you can just double-click that box and it will fill in the series down to your last row of data.  I think you will be pleasantly surprised how often Excel will figure out what values you’d like filled in.  This also works with formulas.  If in cell A1 you have the formula “=B1+C1” you can copy that down to the following rows and Excel will automatically update the references to B2+C2, B3+C3, and so on.

Check out the listing of all the Tuesday Excel Tips.

CopyDownSeries

Posted in Excel | 3 Comments »

Column Width

Posted by jeggent on 1st August 2017

This is part of the Tuesday Excel Tips series.

Adjusting Column Widths – When you first open Excel all of the columns are the same width.  If the entries in a column are longer than the column is wide you may want to adjust the column width to fit.  Or if you’re like me and have lots of columns, you may want to make some columns small to get more on the screen.

There are LOTS of ways to adjust the width of a column or a group of columns, here are some of them:

  1. Put your mouse pointer between the columns and drag the divider to the right or left.
  2. Double click the divider to automatically widen the column enough to fit your longest entry.
  3. Right click on a column header, choose column width, and specify a value.
  4. Click the upper left most corner (above 1 and left of A) to select the entire worksheet, then double click any column divider to adjust all columns to their longest entry.

Check out the listing of all the Tuesday Excel Tips.

AdjustColumnWidth

Posted in Excel | 2 Comments »

Format Painter

Posted by jeggent on 25th July 2017

This is part of the Tuesday Excel Tips series.

Format Painter allows you to apply the formatting of one cell to other cells.  You just select the cell that has the formatting, click on the Format Painter, then select the cells you want to format.  In the example below, cell A1 is bold and has a bottom boarder.  This formatting is applied to the other column titles using the Format Painter.

Check out the listing of all the Tuesday Excel Tips.

FormatPainter

Posted in Excel | 3 Comments »

Excel Tip Tuesdays

Posted by jeggent on 25th July 2017

Every Tuesday for the next couple of months I am going to post a tip to help you be more proficient with Excel.  You can bookmark this page as I will add links to each of the posts as they are published.

Here are the posts that have already been published and some future topics:FormatPainter

2017-07-25:  Using Format Painter
2017-08-01:  Adjusting Column Width
2017-08-08:  Copy Down Series/Formulas
2017-08-15:  Highlighting Cells for Quick Info
2017-08-22:  Quick Formulas
2017-08-29:  Identifying/Removing Duplicates
2017-09-05:  Filters

If you have any tips to share, I would love to hear them in the Comments section!

Cheers,

John

Posted in Excel | 2 Comments »

File Size

Posted by jeggent on 19th February 2016

This is just a public service announcement to save network traffic and file storage space.

When you run a query to Excel from PeopleSoft it will initially be in an Excel 2010 .xls file format.  The query results that I just download had 39,181 rows and 10 columns of data.  As an xls file it had a file size of 6,733 KB.  Without making any other changes I saved the file as an Excel 2013 .xlsx file and it reduced the file size to 1,756 KB.  That is almost 75% smaller.

Thank you for your attention.  You may now return to your memes and cat videos.

Posted in Excel, PeopleSoft, PS Query | 6 Comments »

Excel + PS Query = Search Match

Posted by jeggent on 24th August 2012

I’m working on a project to (pop) update a field in PeopleSoft based on a file from an outside source.  The goofy part is that the file does not have a student ID (not surprising), and the first 5 characters of the SSN are masked (more surprising).  So then how do I do a one-to-one match in order to do the update?  What I’ve come up with had a couple of Excel and PS Query tips that I thought were worth sharing.  The first thing I did was create some new columns in the Excel file that is the source data.  To get just the 4 “good” characters of the SSN, I used the RIGHT function.

Equation:  =RIGHT(B4,4)
Translation:  Give me the last 4 characters from the string (text) in cell B4
Example:  ***-**-1234  = 1234

The name in the source data is listed in this format, Last (comma) (space) first (space) middle initial.  To get just the last name from that string I first used the FIND function to get which character position the “,” is in.

Equation:  =FIND(“,”,C4,1)
Translation:  This looks weird because it is a comma that I’m looking for.  The syntax is FIND then what you are looking for in quotes, what cell to look in, and what character position to start looking from.  So this equation says find the comma in cell C4 and start looking at the first character.
Example:  Student, John A = 8  (the comma is the 8th character in the string (name))

Then I use the LEFT function to get the last name.

Equation:  =LEFT(C4,G4-1)
Translation:  In this example G4 has the results of the Find function.  So this says give me the beginning (Left) characters of the string in cell C4 and stop one character before the number G4 (G4 -1).
Example:  G4 = 8 (see above)  Student, John A = Student (left 7 (8-1) characters in the name)

Next I wanted to use the first three characters in the first name.  I only wanted the first three because of the variability of how first names are recorded.  I used the MID function for this.

Equation:  =MID(C4,G4+2,3)
Translation:   MID is like LEFT and RIGHT except it lets you specify a starting point and an ending point.  This equations says: starting two characters after the comma (remember G4 holds the position of the comma and a space follows the comma, G4+2) give me the next three characters.
Example:  G4 = 8  Student, John A = Joh (three characters starting after the comma and the space)

So, those are fields that I am going to match on in PeopleSoft; the last four of the SSN, the last name, and the first three letters of the first name.

We (thankfully) have a process that allows us to upload a file to PeopleSoft to use in a query.  I uploaded this input file and used some criteria with expressions to do the matching from the file to the PEOPLE_SRCH record to get the student ID.  The last name field is a simple EQUAL TO criteria then I compare the last four of the SSN in the input file to the expression SUBSTR(B.NATIONAL_ID,-4).  The function SUBSTR typically works like the LEFT function in Excel where you specify a field and a number to get the first given number of characters in that text.  However (HOT TIP) if you use a negative number, as I did, it will work like the RIGHT function in Excel and give you the last given number of characters in that text.

For the first name comparison I used the expression SUBSTR(B.FIRST_NAME_SRCH,1,3).  When used with two number parameters SUBSTR works like Excel’s MID, so this expression gets the first (because the first number parameter is 1) three characters of the first name.

I double checked the results and using these three criteria I am able to get the student ID for those in the input file.  While you may never have this same exact situation, hopefully you’ll find parts of this post useful in other projects.

Cheers,
John

Posted in Excel, PeopleSoft, PS Query | 2 Comments »