EXCEL: Deleting Multiple Blank Rows in a Large Spreadsheet

Leave a comment

If you import data from other systems or a user placed blanks rows so that it looked “nice”, you’ll invariably have to manipulate it get it the way you need before you can analyse it especially if you are going to use the Subtotal or Pivot Table. Data interspersed with blank cells/rows is one of the most common problems. Thankfully there is an easy method to get rid of these, like the blank rows below, in seconds. Imagine a large spreadsheet similar to one below:

How to Remove Blank Rows with Go To,
Special Command

  1. Select range of data containing spaces. Taking the example above just the cells A4:A34 (the first column will do).
  2. CTRL+G to open the Go To dialog box.
  3. Click on Special… button
  4. Choose Blanks and Click OK

Now the table should look like this:

 

To proceed with deleting the rows:

  1. Right click on the one of the highlighted cells
  2. Select Delete…
  3. Select Entire Row

Now your table is rid of blank rows!

Advertisements

EXCEL: How to Insert Subtotal Rows

Leave a comment

Many Excel users will sort a specific column and insert empty rows within the table of data and insert the SUM formula to create subtotals. This is perfectly okay if the table only has few rows.  Imagine if you have thousands of rows.  Use the Subtotal command. The Subtotal tool in the Outline group on the Data tab of the ribbon automatically inserts subtotal formulas and grand totals for a column or columns. The subtotals can be SUM’s, AVERAGE’s, COUNT’s, MIN, MAX and more. Click on the following word document for a thorough explanation written by

by MYNDA TREACY on FEBRUARY 4, 2011
in EXCEL,MICROSOFT OFFICE TRAINING,ONLINE TRAINING

http://sdrv.ms/QDgQOM

EXCEL: What’s The Big Deal About Tables in Excel

Leave a comment

Tables help you manage and analyze a group of related data easier.  Therefore, you can turn a range of cells into a Microsoft Office 2007-2010 Excel table (previously known as an Excel list). A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.

To determine if a table is a complete table, just click in cell of that table and click CNTRL+A for Select All. See below:


Notice that the table is highlighted in blue and it is surrounded by empty clear cells like an island. Therefore, if you wanted to sum each quarter or sum by salespeople, you just click in the empty cell and click AutoSum icon. This is also true for sorting a column. If you want to sort by salespeople, all you need to do is click anywhere in the salesperson’s column and click on the Sort icon. No need to highlight the entire table like in the previous versions.

Once you established a table, you can click anywhere in that table and go to Insert Table and state what is the range of the table and that the first row is the header. This command will automatically insert filters, banned rows, and will allow the table to be expand keeping style of the table intact (this is called table expansion). See below:

 

A Table Tool menu appears that allows you to change the design of the table and much more.  The beauty of tables is that the formulas are now in a natural language.  If you use AutoSum, it no longer creates the typical sum formula (for example: =SUM(B4:E4)).  This what it will create:

=SUM(Table1[@[Qtr 1]:[Qtr 4]])

What it says is that in this table which is named Table1 will sum all the values from quarter 1 through quarter 2 for this row. See below:

 

Once you hit ENTER, the sum of the the salespeople are generated instantaneously down to the last row (row 19) of the table. BINGO that easy!No need tocopy down the formula! See below for the results:

 

In the row 19, sum is display.  My suggestion is to delete this word and go to the Table Tool menu and check the box that states Total Row. Excel will automatically place the word Total and will leave a blank row above it. This row does not only sum but if you click in an empty cell, a down arrow symbol appears to the right of the empty cell and you have a choice to choose not only Sum but Average, Count, Count Numbers, Max, Min, StdDev, Var, More Functions. See below:

Lastly, you will notice that there are arrows to right of the headers.  These are the filters where you can query the table quickly.  If you do not like to see these arrows, you can simply remove them by clicking on the Data Tab and click on the icon called Filter.

I commend Microsoft for a job well done on tables.  Go and have fun.  This will excite you.

EXCEL: Different Between Accounting and Currency Formatting

Leave a comment

When formatting numbers, there is a difference between Accounting and Currency formatting. In the earlier versions, the $ icon displayed Currency but it formatted as Accounting. Under the Format Cells NumberWhen applying the formatting here is what you need to remember:

  • Accounting starts with the letter “A” and that means the dollar symbol is “away” and “align” left from the numbers.  For example:
$                    2,000.00
$                      1,00.89
$                          56.99
  • Currency starts with the letter “C” and that means the dollar symbol is “close by”
    For example:

$2,000.00

$1,000.89

$56.99

EXCEL: More Advanced Formulas for Calculating Grades

1 Comment

In my past blog, I described an easy way of calculating grades. In the real world, professors distribute many tests and assignments through the semester.  Here is an example:

The formula in column H calculates each assignment with the weighted percentage. In H1 the formula is as follows:

=(B2*0.15)+(C2*0.15)+(D2*0.15)+(E2*0.15)+(F2*0.2)+(G2*0.2)

The formula in column I is an IF THEN ELSE statement that calculates the grade letter for each student. In I1 the formula is as follows:

=IF(H2>=90,”A”,IF(H2>85,”B+”,IF(H2>=80,”B”,IF(H2>=75,”C+”,IF(H2>=70,”C”,IF(H2>=60,”D”,”F”))))))

After I test these two formulas, I select both formulas and copy it down by doubling clicking on the Autofill handle on the bottom right of the I1 cell (the little black solid icon). See diagram: