Kenneth Barber

My feedback

  1. 7 votes
    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)

      We’ll send you updates on this idea

      Kenneth Barber commented  · 

      I would say that the filter on the slicer's field should only be removed if that field does not appear in the PivotTable. When the field appears in the PivotTable, you have the choice of filtering using the filters on the PivotTable or filtering by slicer, so in these cases, there is no reason why the slicer should dominate.

      Also, any filters can be added or removed from the Field List pane, so in a sense, the current behaviour is just fine.

    • 169 votes
      Sign in
      Check!
      (thinking…)
      Reset
      or sign in with
      • facebook
      • google
        Password icon
        I agree to the terms of service
        Signed in as (Sign out)

        We’ll send you updates on this idea

        Kenneth Barber supported this idea  · 
      • 2 votes
        Sign in
        Check!
        (thinking…)
        Reset
        or sign in with
        • facebook
        • google
          Password icon
          I agree to the terms of service
          Signed in as (Sign out)

          We’ll send you updates on this idea

          Kenneth Barber commented  · 

          OK, now I see what you mean. It's easy to reason through what is going on. Excel fills down until the end of your selection, and since you selected the whole column, it fills the whole column. You instead want to select only the cells that you actually want to be filled.

          This can be easily solved if you use tables. Select your data, go to the Insert tab, and click on Table. When you select table columns, you only get the data and not the blank cells underneath.

          I should also mention that most data that is filled down would have a column heading, so most people wouldn't try to fill the whole column because it would fill the column heading, not the number or formula underneath.

          Lastly, the idea of the last row of your existing data is ambiguous. Suppose that you have data in A1:A10 and D1:D20. Suppose that you enter a value in B1, select column B, and fill down. Should it fill down only to B10 because your value is clearly associated with the A1:A10 range, or should it fill down to B20 because that is the end of all data in your sheet?

          Kenneth Barber commented  · 

          I can't reproduce what you are describing. Can you upload a sample workbook somewhere and give us the link, or give us specific instructions on how to produce this behaviour starting with a blank sheet?

        • 2 votes
          Sign in
          Check!
          (thinking…)
          Reset
          or sign in with
          • facebook
          • google
            Password icon
            I agree to the terms of service
            Signed in as (Sign out)

            We’ll send you updates on this idea

            Kenneth Barber commented  · 

            Is this suggestion for Excel Online?

          • 1 vote
            Sign in
            Check!
            (thinking…)
            Reset
            or sign in with
            • facebook
            • google
              Password icon
              I agree to the terms of service
              Signed in as (Sign out)

              We’ll send you updates on this idea

              Kenneth Barber commented  · 

              You mean just a bunch of borders on a blank 9 × 9 grid? Or do you mean that it should create a Sudoku puzzle for you? Remember that it is possible to create an unsolvable Sudoku puzzle, so you probably don't want to type the numbers in yourself. It's probably better if you just find a Sudoku puzzle generator on the Internet.

            • 1 vote
              Sign in
              Check!
              (thinking…)
              Reset
              or sign in with
              • facebook
              • google
                Password icon
                I agree to the terms of service
                Signed in as (Sign out)

                We’ll send you updates on this idea

                Kenneth Barber commented  · 

                Your post is generalized in the one linked below, since you can have repetitions in formulas in general, not just the IF function.
                https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10099254-support-inline-variables-in-formulas-similar-to-p

                You are right that X will calculate twice, and you can check this yourself using Evaluate Formula in the Formulas tab. To minimize the processing time, extract any repeated calculations into their own cells. If you are worried about the helper cells taking up visual space, you can hide the columns that they are in or move the helper cells to a different sheet.

                I'm not sure what you are getting at in your last paragraph.

              • 34 votes
                Sign in
                Check!
                (thinking…)
                Reset
                or sign in with
                • facebook
                • google
                  Password icon
                  I agree to the terms of service
                  Signed in as (Sign out)

                  We’ll send you updates on this idea

                  Kenneth Barber commented  · 

                  In that case, while you wait for your suggestion to be implemented, you could use the Name Manager to get the syntax that you've suggested.

                  If you want the end of column A, define a name "A" whose formula is =INDEX(A:A,ROWS(A:A)). Then you can literally just write A2:A like in your suggestion, and it will work.

                  Rows are a bit different, since you can't have a name that is a number, but the same concept applies.

                  The disadvantage is that you would need 1 name per column whose end that you want.

                  Kenneth Barber commented  · 

                  I still say that you should put your data into a table (select your data, and then Insert tab → Table or Ctrl + T). Tables extend only as far down as you let them, and as the number of rows in the tables changes, the references to the table columns do not change.

                  Conditional formatting doesn't support the table style of reference (e.g. Table[Column]), so you will still have to use the A1:A2 style of reference for your columns. However, the conditional formatting is smart enough to change the column reference as the number of rows changes.

                  Give it a try!

                  Kenneth Barber commented  · 

                  You can already do this. Use Insert tab → Table or Ctrl + T to transform your data into a table. Then you can reference the table column rather than the sheet column.

                  If you cannot use a table, use a reference like A2:INDEX(A:A,ROWS(A:A)).

                  Explanation:
                  It's not just cell references that are valid in the A1:B2 syntax. Anything that returns a reference can be used. In this case, we have A2:INDEX(). INDEX returns a reference to the cell at a given position (ROWS(A:A)) in the given array (A:A). ROWS(A:A) is the number of rows in column A. Using it in INDEX gives us a reference to the last cell in column A.

                • 3 votes
                  Sign in
                  Check!
                  (thinking…)
                  Reset
                  or sign in with
                  • facebook
                  • google
                    Password icon
                    I agree to the terms of service
                    Signed in as (Sign out)

                    We’ll send you updates on this idea

                    Kenneth Barber commented  · 

                    You are right that tables are supposed to get rid of the A1 notation, hence the "current row" ([@[Column]]) syntax. This, combined with the "whole column" syntax, help to achieve another goal of tables: the order of the rows shouldn't matter. This means that you can sort your table, move rows around, etc. and the table still works the same.

                    The way that you are using the tables, you are forcing both tables to be in the same order as each other. They're not meant for that.

                    And I still don't understand in what case you would need 2 tables to have a 1:1 relationship with each other, with the corresponding rows being in the same position within their respective tables, and where you can't/shouldn't combine them into a single table.

                    Kenneth Barber commented  · 

                    I think that you are misunderstanding the structured reference syntax.

                    The Table[Column] syntax is a reference to the entire table column. Really, I would think that if a single cell uses =Table[Column], it shouldn't return anything, since it doesn't make sense for a single cell to return an entire column.

                    The Table[@Column] syntax is a reference to the cell in Table[Column] that is in the same sheet row as the cell that is using the formula. Usually, you only use [@Column] within the same table as Column.

                    If you need a column to reference all of the values in a column in another table in the exact same order, why not just combine your tables?

                    If you cannot combine your tables, here is the solution to your problem. Suppose that you want values from Table1 to be referenced in Table2. In Table2, add a column called "Row". In the Row column, use the formula =ROW()-ROW([Row])+1 so that your rows are numbered 1, 2, 3, etc. In the column that is supposed to reference Table1[Column], use the formula =INDEX(Table1[Column],[@Row]) to return the cell in Table1[Column] at position [@Row].

                  • 1 vote
                    Sign in
                    Check!
                    (thinking…)
                    Reset
                    or sign in with
                    • facebook
                    • google
                      Password icon
                      I agree to the terms of service
                      Signed in as (Sign out)

                      We’ll send you updates on this idea

                      Kenneth Barber commented  · 

                      I meant that all of the clicking through the widget, even if the widget is perfectly responsive, might be slower than just typing the date in.

                      Kenneth Barber commented  · 

                      The widget might be more user-friendly, but it would also be slower in a lot of cases.

                    • 1 vote
                      Sign in
                      Check!
                      (thinking…)
                      Reset
                      or sign in with
                      • facebook
                      • google
                        Password icon
                        I agree to the terms of service
                        Signed in as (Sign out)

                        We’ll send you updates on this idea

                        Kenneth Barber commented  · 

                        Which templates?

                        Kenneth Barber commented  · 

                        What do you mean?

                      • 1 vote
                        Sign in
                        Check!
                        (thinking…)
                        Reset
                        or sign in with
                        • facebook
                        • google
                          Password icon
                          I agree to the terms of service
                          Signed in as (Sign out)

                          We’ll send you updates on this idea

                          Kenneth Barber commented  · 

                          It's a bit hard to follow your process, both in the original post and your comment. Do you have an example workbook that you can upload somewhere and can you post a link to it here?

                          I should also mention that Power Query (AKA Get & Transform Data) probably has something for filtering for unique rows.

                          From what I gather, maybe MATCH or VLOOKUP will help you? Use MATCH on one table to look into the other AND vice versa. MATCH will return #N/A if it doesn't find a match. Your lookup value would be the concatenation of the content of all of your rows.

                          Kenneth Barber commented  · 

                          What Advanced Filter criteria do you use? I'm wondering if a PivotTable could help solve your problem.

                        • 14 votes
                          Sign in
                          Check!
                          (thinking…)
                          Reset
                          or sign in with
                          • facebook
                          • google
                            Password icon
                            I agree to the terms of service
                            Signed in as (Sign out)

                            We’ll send you updates on this idea

                            Kenneth Barber supported this idea  · 
                          • 2 votes
                            Sign in
                            Check!
                            (thinking…)
                            Reset
                            or sign in with
                            • facebook
                            • google
                              Password icon
                              I agree to the terms of service
                              Signed in as (Sign out)

                              We’ll send you updates on this idea

                              Kenneth Barber supported this idea  · 
                            • 1 vote
                              Sign in
                              Check!
                              (thinking…)
                              Reset
                              or sign in with
                              • facebook
                              • google
                                Password icon
                                I agree to the terms of service
                                Signed in as (Sign out)

                                We’ll send you updates on this idea

                                Kenneth Barber commented  · 

                                Save all of your files as XLSB and you should see a huge size decrease.

                              • 1 vote
                                Sign in
                                Check!
                                (thinking…)
                                Reset
                                or sign in with
                                • facebook
                                • google
                                  Password icon
                                  I agree to the terms of service
                                  Signed in as (Sign out)

                                  We’ll send you updates on this idea

                                  Kenneth Barber commented  · 

                                  I don't think that you are using tables as they were intended. If your 1:00 and 12:00 are times, then they should become their own Time column. Also, if your TRUE table and your FALSE table have the same columns, then you could combine them and distinguish them using a TRUE/FALSE column. This way, all of the types of data that give context to your values get their own column, and they only grow downwards. It is also much more consistent and you don't have to write weird formulas to switch between columns or tables. There are a few straightforward techniques to do multi-criteria lookups. I can tell you them if you are interested.

                                  Kenneth Barber commented  · 

                                  I'm not following. What are the formulas in each cell?

                                  Kenneth Barber commented  · 

                                  Interesting... Can you give a real-world example of when you'd use this?

                                  Kenneth Barber commented  · 

                                  I can see this being useful for a cumulative sum, but you have to remember that the very first formula cell in the column will not follow the "cell at previous row + cell at current row" pattern, since there is no previous row for the first cell. This would have to be explicitly dealt with in the formula. Your formula would look something like:
                                  =[@[Cost]]+IF(ROW()=ROW(INDEX([Cumulative Cost],1)),
                                  0,
                                  INDEX([Cost],ROW()-ROW(INDEX([Cost],1)))

                                • 3 votes
                                  Sign in
                                  Check!
                                  (thinking…)
                                  Reset
                                  or sign in with
                                  • facebook
                                  • google
                                    Password icon
                                    I agree to the terms of service
                                    Signed in as (Sign out)

                                    We’ll send you updates on this idea

                                    Kenneth Barber commented  · 

                                    • The number argument of SIN, COS, TAN, CSC, SEC, and COT should be angle_in_radians.
                                    • The serial_number argument of YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEKDAY, and WEEKNUM should be datetime.

                                    Kenneth Barber supported this idea  · 
                                    Kenneth Barber shared this idea  · 
                                  • 1 vote
                                    Sign in
                                    Check!
                                    (thinking…)
                                    Reset
                                    or sign in with
                                    • facebook
                                    • google
                                      Password icon
                                      I agree to the terms of service
                                      Signed in as (Sign out)

                                      We’ll send you updates on this idea

                                      Kenneth Barber commented  · 

                                      OK, now I understand what you are saying. You expected YEAR to return the current year, but instead it requires a "serial_number" argument. If it would have said "datetime", then it would have been more obvious that you had to go =YEAR(TODAY()).

                                      So part of this suggestion is "make YEAR return the current year by default", and the other part is to make the function argument names less cryptic (suggestion linked below).
                                      https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/14665389-increase-user-friendliness-of-function-argument-na

                                      Kenneth Barber commented  · 

                                      What are you even suggesting/asking?

                                    • 3 votes
                                      Sign in
                                      Check!
                                      (thinking…)
                                      Reset
                                      or sign in with
                                      • facebook
                                      • google
                                        Password icon
                                        I agree to the terms of service
                                        Signed in as (Sign out)

                                        We’ll send you updates on this idea

                                        Kenneth Barber commented  · 

                                        ebola varss

                                      • 1 vote
                                        Sign in
                                        Check!
                                        (thinking…)
                                        Reset
                                        or sign in with
                                        • facebook
                                        • google
                                          Password icon
                                          I agree to the terms of service
                                          Signed in as (Sign out)

                                          We’ll send you updates on this idea

                                          Kenneth Barber commented  · 

                                          AutoSum is a button that gives you quick access to functions like SUM and AVERAGE. The shortcut for AutoSum is Alt + =.

                                        • 2 votes
                                          Sign in
                                          Check!
                                          (thinking…)
                                          Reset
                                          or sign in with
                                          • facebook
                                          • google
                                            Password icon
                                            I agree to the terms of service
                                            Signed in as (Sign out)

                                            We’ll send you updates on this idea

                                            Kenneth Barber commented  · 

                                            Instead of pasting cell by cell, select all of the cells that will be pasted into, and then paste just once.

                                          ← Previous 1 3 4 5 37 38

                                          Feedback and Knowledge Base