블로그 이미지

카테고리

shonini (60)
Quotes (0)
Refinement (2)
My Interests (11)
Aluminum (17)
Mac (4)
MS Office (6)
MBA (5)
Travel (5)
Design (10)
Total43,316
Today7
Yesterday16




Protected View 모든 박스 체크 해제







General => 체크 해제







'MS Office > Word' 카테고리의 다른 글

Disable opening in protected view  (0) 2014.09.29
Posted by shonini

댓글을 달아 주세요

Pivot Tip 7 - Tips on formatting a pivot table

David Carter's series of Five Minute Tips aims to develop your skill at using Excel pivot tables. To test these formating tips for yourself, try them out on your copy of the pivot_practice.xlsdatabase.

The beauty of Excel is that you can format a spreadsheet any way you want. It's not quite so easy in pivot tables, and occasionally you can modify a pivot table only to lose the formatting when you recalculate via the Refresh option. Microsoft has added so many options over the years that it gets confusing. These tips should clarify matters.

Use the black down arrow to highlight columns and rows
At the top of the first column in your pivot table is a grey Field button giving the name of the field in the Row area. Usually it will be in about cell A4; in our example it's called Product or Customer.

If you wave your mouse pointer above this grey field button a thick black down arrow should appear. Left click and it highlights the whole column for formatting.

Similarly, if you wave the down arrow over the other grey field buttons, it will highlight column headings etc too.

Enable Selection
This black arrow automatically highlights an area for you to format. Sometimes, however, the Enable Selection feature gets turned off and the arrow disappears.

To see how it works, right click on the pivot table for menu. Choose Select. At the bottom is the Enable Selection button.

Click on Enable Selection. The down arrow will now no longer appear over the field button.

Now go back and choose Select'Enable Selection. The down arrow re-appears.

How to Activate the other Select options
When you go into the Select menu, you see that the top three options are greyed out. To activate them, click onto Entire Table.

This highlights the entire pivot table. Now go back into Select once more: the three options are now activated. They allow you to highlight part or the whole of the pivot table.

Format a column of numbers
The selection down arrow doesn't work with columns of numbers.

You can manually highlight them with a mouse, then apply formating. However, you might find it quicker to right click on any number in the column, then from the menu select Field Settings'Number.

The formatting you now choose will be applied to all the figures in the column.

Setting column widths
You cannot define the width of a column in a pivot table (if you use Format'Columns'Width the columns will re-set themselves as soon as you Refresh).

Excel estimates the width of the columns from the data or the column heading. So sometimes you have to be a bit inventive to make sure that your columns widths will stay that way even after you refresh.

Making a column narrower
You can type in a new column heading over the existing one. 
For example, one of the column headings we created in the last article was 'Sum of Margin Percent', which was far too wide.

Note, however, that if you just remove "Sum of" and change the column heading to 'Margin Percent', Excel will send an error message that 'Pivot table Name already exists' because this is the name of a data field.

Personally, I usually remove 'Sum of' to make a column narrower, but hit the space bar once as well so that the heading is not the same as the field name.

Making columns the same width
One irritating result of Refreshing the data can be that columns all now have different widths.

Sometimes you can control the width of all the number columns by modifying the column heading in the first column.

For example, if the column headings are Apr, May, Jun, Jul etc, go into the first column heading and change Apr to Apr-2005.

Now refresh the data. All the columns become wider, not just the April one.

Format Report
Finally, of course, you can let Excel do all your formatting for you.

Right click on the pivot table to call up the menu, the second option is Format Report. This offers you numerous pre-set formats. Choose any one to reformat the entire pivot table.

Related material in ExcelZone
For more than four years, David Carter and AccountingWEB members have built up a massive stockpile of material on Excel and pivot tables. To delve more deeply into the subject, see:

  • David Carter's Five Minute Pivot Table Tips - index
  • Want to learn about Excel pivot tables? Start here
  • The Excel Compendium - Pivot Tables
  • 100 Best Time-Saving Ways to Use Microsoft Office by Simon Hurst

  • http://www.accountingweb.co.uk/topic/technology/pivot-tip-7-tips-formatting-pivot-table

    'MS Office > Excel' 카테고리의 다른 글

    Pivot table name already exists.  (0) 2014.06.20
    엑셀 실무활용하기  (0) 2014.04.16
    Posted by shonini

    댓글을 달아 주세요


    GO Contact Sync Mod



    The Settings Dialog

    Description

    GO Contact Sync Mod synchronizes your Microsoft Outlook contacts with your Google Mail address book, including pictures, categories and notes. Since version 3.5.6 it also syncs your Outlook Notes on demand to Google Documents. This Mod fork adds many enhancements and bug fixes to the obviously dead project 'Go Contact Sync'.

    Please find some guidelines how to sync your mobile phone contacts with Google Contacts here:http://www.google.com/mobile/sync

    GO Contact Sync Mod Web Site



    GCSMSetup_3_7_3.msi


    Categories

    Features

    • Outlook Contacts sync
    • Automatic syncing
    • Pictures support
    • Categories support
    • Go Contact Sync fork
    • Contact notes are synchronized (size limited to 62.000 characters)
    • Supports Outlook 2003, 2007 and 2010 (32 and 64 bit)
    • No Support for Outlook Express
    • Outlook notes sync
    • Select Outlook folder to sync
    • Sync with multiple GoogleMail accounts


    http://sourceforge.net/projects/googlesyncmod/



    https://productforums.google.com/forum/#!topic/gmail/Zy2HEli5RmI


    'MS Office > Outlook' 카테고리의 다른 글

    Outlook / Gmail Contact Sync  (0) 2014.05.26
    How to set up Gmail in Outlook 2013  (0) 2014.04.16
    Posted by shonini

    댓글을 달아 주세요

    How to set up Gmail in Outlook 2013

    Written by  on January 20, 2013 in Software

     

     

     

    With an intuitive interface and a plethora of useful features, Gmail is no doubt the best platformto manage your mails. Still, there are reasons why people would want to set up their Gmail account in Microsoft Outlook. Like, when you have several email accounts to manage, you will prefer a single platform on Microsoft Outlook to do that. You might also want to use outlook as a backup software to store your Gmail messages locally on your computer.

    For whatever the reasons, here’s how you can set up your Gmail in Outlook 2013 using either POP3 or IMAP.

    POP3 and IMAP: Which one to choose?

    POP3 and IMAP are both protocol that allows you to connect to a mail server and fetch your emails. With POP3, all your emails are downloaded to your computer first before you can view them in an email client such as Microsoft Outlook or Thunderbird. When you read an email in Outlook, your email will be marked as read automatically in the software, but it will still be marked as unread on the mail server, e.g. in your Gmail account. The same applies if you delete an email in Outlook – you will still see the email through the web-based version in Gmail, although you have already deleted it in Microsoft Outlook.

    In contrast with POP3, IMAP syncs all the changes you’ve done on your mail client (Microsoft Outlook). You should use IMAP if you access your email through multiple devices such as your office computer, laptop, tablet or your mobile phone. This is because if you have deleted an email when you were accessing your inbox on your laptop, and then log into your inbox again on your tablet, you wouldn’t want to see emails that you have previously deleted, right? Your previously read emails will also be marked as read across all your devices.

    I recommend you to use IMAP over POP3 as it will save you both time and space if you use multiple devices to reach your inbox. If you intend to use Outlook for back up purposes, then use POP3.

    Set up Gmail using POP3

    1.Before you set up Microsoft Outlook, you will first need to enable POP on your Gmail Account. To do that, navigate to the small gear-shaped icon at top right corner of your screen. Click on Settings from the drop down menu. Then go to Forwarding and POP/IMAP tab.

    Gmail in outlook 2013

    2.In the second section related to POP download, enable POP and choose from the first two options, Enable POP for all mail or Enable POP for mail that arrives from now on.

    Depending on what your needs are, tweak the settings related to action taken when mail is accessed with POP.

    3.Start Microsoft Outlook and navigate to File at the top and click on the button Add Account under Account Information.

    How to set up Gmail in Outlook 2013

    4.In the Add account window, check the option at the bottom, manually configure server settings, and then click next.

    5.Select the first option Internet Email and click next.

    6.Enter your name and email address under user information. In the server Information, select POP3 as the account type. Enter the incoming and outgoing mail server. For Gmail, the incoming mail server is pop.gmail.com and outgoing server is smtp.gmail.com. Under the Login information, enter your username (your Gmail email address) and your Gmail password.

     

    Setting up Gmail in Outlook 2013


    Setting up Gmail in Outlook 2013 – (Click to Enlarge)

     

    7.Now, go to More Settings. Navigate to Outgoing server and enable the first option My Outgoing server requires authentication. Select the first option Use same settings as my incoming mail server.

    Advanced setttings in Gmail Outlook 2013

    8.Navigate to Advanced tab and enter the appropriate port numbers. For the incoming mail server, enter 995 and enable the option this server requires an encrypted connection. In the outgoing mail server, enter the number 465 and choose SSL from the drop down list.

    Gmail port settings for Outlook 2013

    9.Once you’re done with that, click OK and return to the Gmail settings window. Under the Test Email Accounts settings, enable the option Automatically test account settings when Next is clicked.  Then click on  Next. Outlook will send a test email message to your account. If the email was set up successfully, you will receive the test email in your inbox.

    Test Gmail settings in Outlook 2013

    Once you set it up successfully, you don’t need to fuss with the settings again. It’s time to sit back and relax while you can use the simple interface of Microsoft Outlook to send and receive emails from your Gmail address.

    Set up Gmail using IMAP

    Setting up Gmail in Outlook 2013 using IMAP is almost the same as for POP3 except that it uses a different incoming mail server address and ports.

    1.Repeat step 1 as for POP3 and next to IMAP access in your Gmail account, chooseEnable IMAP.

    Enable IMAP in Gmail account

    2.Repeat step 2-5. In the Add Account window, select IMAP as your account type.

    Use the following settings:

    Incoming mail server: imap.gmail.com
    Outgoing mail server: smtp.gmail.com

    Enable imap in gmail account

    3.Then click on More Settings, go to Advanced tab.

    Use the following settings:

    Incoming server(IMAP): 993
    Type of encrypted connection: SSL

    Outgoing server(SMTP): 25
    Type of encrypted connection: TLS

    Imap port settings for gmail in Outlook

    Then click Ok.

    4.Now you should be back on the Add Account screen. Click Next to complete the set up of your Gmail account in Outlook 2013.

    Fetching your emails

    Whatever the protocol you have chosen above to connect with your Gmail in Outlook 2013, the steps to fetch your emails from Gmail servers is the same. On your Outlook main window, click on Send/Receive tab at the top. Then click on Send/Receive all Folders.

    Fetch emails outlook 2013

    That’s all folks! I know it’s a long process in order to set up Gmail in Outlook 2013 but it’s worth the pain.

    'MS Office > Outlook' 카테고리의 다른 글

    Outlook / Gmail Contact Sync  (0) 2014.05.26
    How to set up Gmail in Outlook 2013  (0) 2014.04.16
    Posted by shonini

    댓글을 달아 주세요



    목록별로 입력하기! (이중 유효성 검사이용)

    1. 모델목록있는 셀에 ="시트명!$C$3:$C$"&COUNTA(C3:C100)+2 입력하기

    2. 다른 모델목록에 ="시트명!$D$3:$D$"&COUNTA(C3:C100)+2 변경하기

    3. 분류항목을 " 분류 " 라고 이름정의한다.

    4. 주소값과 모델명 셀부분 범위지정한 후 " 모델명 " 이라고 이름정의한다.

    5. 목록별로 입력할 시트로 이동 후 분류열 범위지정 후 데이터/유효성검사 클릭한다.

    6. 제한대상에서 " 목록 " 선택하고, 원본란에 " =분류 " 라고 입력한 후 확인한다.

    7. 모델명열을 범위지정 후 데이터/유효성검사 클릭한다.

    8. 제한대상 "목록" 선택 후 원본란에 "=INDIRECT(HLOOKUP(C1,모델명,2,0)) " 라고 입력한다.

    9. 오류상태 안내 무시한다.

    10. 분류명 선택하면 모델명은 분류에 해당하는 모델명만 나오게 된다.

     

    목록 채우기

    1. 직접 입력하여 정의하기

    - 도구/옵션/사용자지정목록 들어가기.

    - 목록 입력한 후 "확인"하기

    - 첫 목록 셀에 입력 후 채우기 핸들

    2. 가져오기

    - 입력되어 있는 목록 -> 목록에 등록하기

    - 목록 가져올 범위

     

    데이터 가져와서 목록별로 입력하기 (유효성검사)

     

     

     

    규칙적인 날짜 손쉽게 입력하기

    - 첫날짜 셀 B4에 3일간격 입력한다면

    - 두번째 날짜 셀에 =B4+3 입력하기

    - 셀참조복사하기

     

    행과행, 열과열 위치 바꾸기

    - 옮길 행 범위지정 후 테두리영역에 위치한 후

    - Shitf를 누른 상태로 옮길 열 사이의 경계선에 드래그하면 위치변경됨.

     

    행과 열 바꾸기

    - 전체복사하기

    - 선택하여 붙이기 / 행열바꿈 체크

     

    반복되는 "0" 한꺼번에 입력하기

    - 복사할 데이터 "10000" 아무셀에 쓰기

    - 복사하기

    - 선택하여 붙이기/연산,곱하기 선택

     

    데이터 합계값으로 통합하기

    - 데이터값만 복사하기

    - 복사할 영역 범위지정 후 선택하여 붙이기

    - 연산 / 더하기 선택

     

    그림으로 붙여넣기

    - 복사하기

    - Shift 키 누른체로 붙여넣기 선택

    - 그림으로 붙여넣기 선택 (원본수정반영안됨)

    - 연결하여 붙여넣기 선택 (원본수정반영됨)

     

    행 삽입/삭제하기

    - 삽입될 곳의 아래행 선택 후

    - Ctrl + 누르기 (계산기 + 누르기) - 행삽입

    - 삽입될 곳의 아래행 선택 후

    - Ctrl - 누르기 (계산기 - 누르기) - 행삭제

     

    표시형식

    #,##0"원" - 2자리마다 쉼표찍고 "원" 단위 붙이기

    #,##0,,"백만원" - 3자리 생략 후 남은 수 세자리마다 쉼표 찍고 " 백만원" 단위 붙이기

    - 표시형식만 바꾸고 원본데이터 변경 안됨, 수식적용시에 오차줄이기 위함

    #,##0;[빨강]#,##0;"-"  (양수의서식;음수의서식;0의서식)

    - 양수는 쉼표찍고 표현, 음수는 빨강색 쉼표찍고 표현, 음수는 "-"로 표현

    - 숫자를 한글이나 한자로 표현할때(셀서식/표시형식/기타)

     

    조건부서식

    입금기한보다 입금일이 초과되거나 같을 때 셀서식 달리하기

    - 첫행에서 아래로 복사, 조건부서식을 위해서!

    - =OR($G4<$H4,$H4="") G4=입금기한 H4=입금일

    토요일 파란색, 일요일 빨간색으로 적용하기

    - =WEEKDAY($B4)=7, 토요일경우 셀서식 파란색 적용

    - =WEEKDAY($B4)=1, 일요일경우 셀서식 빨간색 적용

    - WEEKDAY 타입생략할 시 일요일1, 월요일2 ... 적용됨

    배달지역중 " 도봉 " 을 포함하면 셀색깔 하늘색으로 서식적용

    - =ISERROR(SEARCH("*도봉*",$ㅎ4))=FALSE

    - SEARCH(찾으려는 텍스트,찾을위치,시작위치)

    - 시작위치 생략가능, 생략하면 찾을위치의 첫번째부터 검색

     

    5행 고정하고 B열 고정하여 셀참조

    = C$5-(C$5*$B6)

    - 원가격에 할인율 곱해서 원가격에서 차감한 금액이 할인금액이 된다.

     

    수식보호하기

    - 행머리글과 열머리글 교차지점클릭 후 마우스 오른쪽 셀서식

    - 셀서식/보호/ 에서 "잠금","숨김" 해제

    - 보호하고 싶은 수식범위지정 후 셀서식/보호/에서 "잠금","숨김" 체크

    - 도구/보호/시트보호 클릭 후 시트 보호 해제 암호 지정하기

    - 수식 숨겨지고, 데이터 지우려하면 경고창이 뜬다. 


    'MS Office > Excel' 카테고리의 다른 글

    Pivot table name already exists.  (0) 2014.06.20
    엑셀 실무활용하기  (0) 2014.04.16
    Posted by shonini

    댓글을 달아 주세요



     "시작" → "실행" 

    1. "regedit"을 입력하고 확인

    2. 아래 적은 위치까지 레지스트 폴더를 연다
    "HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\PowerPoint\Options"

    3. "편집"메뉴의 "새로 만들기"를 선택 후, "DWORD"값을 누릅니다.

    4. "ExportBitmapResolution"을 입력한 다음 엔터

    5. "ExportBitmapResolution"을 선택 => 편집 메뉴 => 수정

    6. "10진수"를 선택 후 "307" 입력하고 확인

    7. 레지스트편집기를 종료하고 이제 파워포인트를 실행시켜 JPG파일로 저장
    이제 300Dpi의 "3000 x 2250" 크기의 고화질 JPG파일을 생성된 것을 확인할 수 있습니다.

    ※ 경고 : 레지스트리 편집기를 잘못 사용하면 심각한 문제가 발생할 수 있으며 문제를 해결하기 위해 운영 체제를 다시 설치해야 할 수도 있습니다. Microsoft는 레지스트리 편집기를 잘못 사용하여 발생하는 문제에 대해 해결을 보증하지 않습니다. 레지스트리 편집기 사용에 따른 모든 책임은 사용자에게 있습니다. 


    Posted by shonini

    댓글을 달아 주세요

    최근에 달린 댓글

    최근에 받은 트랙백

    글 보관함

    01-27 12:42