Main Page

Entries tagged [ms_excel]


Biow Tutorial: How to append cell values in Excel, how to add single quote to a list for sql query



Many times during programming, we may need to do some sql query. Be it to insert some pre-loaded values or to retrieve some results.. and we were given a list of names, to find out perhaps their address, title, etc from a table.. what is the easiest way? I find Excel is the solution for me.. here's how to do it.. you may download this file in Excel to better understand my explaination.

 
Step1:
Excel
 
 
Result:
Excel





(2010-08-11 11:05:09 SGT) [biow's tutorial] Permalink



Biow Tutorial: How to Find the Number of Days, Months, and Years Between Two Dates in MS Excel Tutorial



Question: How to Find the Number of Days, Months, and Years Between Two Dates in MS Excel?

Answer: Assuming your date1 is in cell A1 and date2 in cell A2, type the following in the B1 cell:

=YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2) <=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2) >=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months, " &A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"


See also:

  1. Set header and footer as an Excel template Tutorial
  2. Remove duplicate data or records, create list of unique data or records in Excel 2003
  3. Tutorial: Black and White with some parts colored using Photoshop CS3
  4. Tutorial: Adding copyright watermark to your images using Photoshop
  5. Biow Tutorial: Animate series of images with Photoshop CS3
  6. Tutorial: Reflection effect in PS
  7. Reference: Photoshop Magnifying Glass Tutorial
  8. Reference: Add Speech Balloons and Text Bubbles to Your Photos in Photoshop or Elements Tutorial



(2010-05-18 10:52:13 SGT) [biow's tutorial] Permalink



Biow Tutorial: Set header and footer as an Excel template Tutorial



Question: Each time I launch MS Excel, I want it opened with my personal/customized/preferred formatting?
How can I do that?

Answer: follow the steps below..

Step 1. Launch MS Excel

  1. Select all cells by clicking on top left corner.
  2. Change font / font size to your preference
  3. Optional: delete all other worksheets and left with Sheet1


Step 2. Change settings on Page Setup

  1. File > Page Setup...


  2. Settings for Page tab:


  3. Settings for Margins tab:


  4. Settings for Header/Footer tab - Header:


  5. Settings for Header/Footer tab - Footer:


  6. Let's Print Preview to ensure the final look and feel:


Step 3. Save as default template

  1. Save As... Book.xlt to folder C:\Program Files\Microsoft Office\Office 11\XLStart\


  2. Save As... Sheet.xlt to folder C:\Program Files\Microsoft Office\Office 11\XLStart\
    This is so that when you need to Insert worksheet, the same preferred formatting will be preserved. In case you can't save to
    C:\Program Files\Microsoft Office\Office 11\XLStart\
    due to some company's policy, you can save to
    C:\Documents and Settings\biow\Application Data\Microsoft\Excel\XLSTART\
    where biow should be replaced with user name to Windows.


Step 4. Let's try it

  1. Close the MS Excel window.
  2. Launch MS Excel.
  3. Taa-daa.. your new Worksheet's formatting is as you've set above.

That's it. Great tutorial, ya? keke.. *pat myself*..



See also:

  1. Remove duplicate data or records, create list of unique data or records in Excel 2003
  2. Tutorial: Black and White with some parts colored using Photoshop CS3
  3. Tutorial: Adding copyright watermark to your images using Photoshop
  4. Biow Tutorial: Animate series of images with Photoshop CS3
  5. Tutorial: Reflection effect in PS
  6. Reference: Photoshop Magnifying Glass Tutorial
  7. Reference: Add Speech Balloons and Text Bubbles to Your Photos in Photoshop or Elements Tutorial



(2009-12-15 15:24:44 SGT) [biow's tutorial] Permalink



Biow Tutorial: how to print out Excel comments tutorial



Do you have any idea how to print out Excel comments? well I have no freaking idea.. i tried the Excel Help.. sucks.. dunno searching what Microsoft Online help.. so freaking slow.. i googled, and the first result explains all.. so, here it is as public service.. keke.. mabbe i'm the only IT person who do not know until today.. *blush*..

Reference:
http://www.contextures.com/xlcomments01.html



(2008-01-19 10:00:00 SGT) [biow's tutorial] Permalink








Search

Sponsored Links



Categories

Tags

art_n_craft

cny

comic

cut-off

cut-off_jc

cut-off_psle

cut-off_uni

education

family

flash

k-drama

maths

movies

mr_clever

mr_earnest

mr_steady

ms_chatterbox

ms_excel

office2003

p1

p2

p3

pet

psle

school

science

singapore

travel

tutorial

windows
Links


Navigation

Front Page
Weblog
TagsIndex
Privacy Policy
Login
Decals





rss feed for in Biow's World..

powered by Roller