Excel Inspiration: Excel Tips

Showing posts with label Excel Tips. Show all posts

20 Aug 2013

Insert the rows in between the name list:

1. Add new "helper column"
2. Type "A"&"B" in helper columns cells
3. Select both cells & Drag it down
4. Data --> Filter ---> Select "A"
5. Select helper column
6. Press "Alt ;" to select only visible cells
7. Press Ctrl+Shift+Plus and Select "Entire Row" then Press OK
8. Data --> Filter ---> Select "B"
9. Select helper column
10. Press "Alt ;" to select only visible cells
11. Press Ctrl+Shift+Plus and Select "Entire Row" then Press OK

Check this link, http://www.screenr.com/J6mH

5 Jun 2012

Microsoft Excel Function Reference

Database Functions

DAVERAGE(database,field,criteria)

Averages the values in a column in a list or database that match conditions you specify.

DCOUNT(database,field,criteria)

Counts the cells that contain numbers in a column in a list or database that match conditions you specify.

DCOUNTA(database,field,criteria)

Counts all of the nonblank cells in a column in a list or database that match conditions you specify.

DGET(database,field,criteria)

Extracts a single value from a column in a list or database that matches conditions you specify.

DMAX(database,field,criteria)

Returns the largest number in a column in a list or database that matches conditions you specify.

DMIN(database,field,criteria)

Returns the smallest number in a column in a list or database that matches conditions you specify.

Rows to Repeat

1. Excel having options for "Rows to Repeat at Top" - Press Alt + P + I, Select the Rows and OK.

2. There is no in-build function for "Rows to Repeat at Bottom", You can try below codes to show it in footer.
Sub MyFooter()

Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range

Set Sh = Worksheets("Sheet1")

Set Rng = Sh.Range("A20:H20")

For Each c In Rng

StrFtr = StrFtr & c & " "

Next c

ActiveSheet.PageSetup.LeftFooter = StrFtr

End Sub

14 Sept 2011

How Big Is a Worksheet?

It's interesting to stop and think about the actual size of a worksheet. Do the arithmetic (16,384 × 1,048,576), and you'll see that a worksheet has 17,179,869,184 cells. Remember that this is in just one worksheet. A single workbook can hold more than one worksheet. If you're using a 1024 × 768 video mode with the default row heights and column widths, you can see 15 columns and 25 rows (or 375 cells) at a time - which is about .000002 percent of the entire worksheet. In other words, more than 45 million screens of information reside within a single worksheet.If you were to enter a single digit into each cell at the relatively rapid clip of one cell per second, it would take you about 545 years, nonstop, to fill up a worksheet. To print the results of your efforts would require more than 40 million sheets of paper - a stack more than a mile high. As you might suspect, filling an entire workbook with values is not possible.

2 Sept 2011

Useful Excel Websites

Microsoft (MS) Excel is a powerful spreadsheet that is easy to use and allows you to store, manipulate, analyse, and visualize data. There are many websites are available to learn & share our knowledge. The below websites provides you several online help options, such as interactive tutorials and topic specific searchable help files.

http://peltiertech.com/Excel/ChartsHowTo/index.html

http://www.dzikosoft.com/gmexcel/

http://akoul.blogspot.com/

http://exceldailytip.blogspot.com/

http://www.vbamacros.blogspot.com/

http://www.cpearson.com/excel/MainPage.aspx

http://www.excelhero.com/

http://exceltnt.blogspot.com/

http://chandoo.org/

http://shahshaileshs.web.officelive.com/default.aspx

http://www.yogeshguptaonline.com/

http://www.computergaga.com/excel/msxl_training.html