블로그 이미지

카테고리

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

'MS Office/Excel'에 해당되는 글 2건

  1. 2014.06.20 Pivot table name already exists.
  2. 2014.04.16 엑셀 실무활용하기

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

    댓글을 달아 주세요



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

    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

    댓글을 달아 주세요

    최근에 달린 댓글

    최근에 받은 트랙백

    글 보관함

    01-27 13:52